String Functions on Unnested Arrays in BigQuery SQL-CodePudding

I'm trying to find all instances of rows where one of the array items contains a value, ignoring the case in Google BigQuery.

I'm able to do this:

SELECT
  COUNT(*)
FROM
  `my_bq_project.my_bq_dataset.my_table`
WHERE
  (
      'AAA' IN UNNEST(array_1.array_2.some_text)
  )

This works well for finding 'AAA' inside array_1.array_2.some_text. But I would like to also find 'aaa', 'Aaa', and so forth.

But this does not work:

SELECT
  COUNT(*)
FROM
  `my_bq_project.my_bq_dataset.my_table`
WHERE
  (
      'AAA' IN UNNEST(LOWER(array_1.array_2.some_text))
  )

And I would like to refrain from duplicating the where statement (adding where conditions for 'AaA', 'aaa', etc.).

Is there a way to apply string functions like LOWER on struct fields that are in arrays in BigQuery SQL?

Thanks

CodePudding user response：

Try below approach

SELECT
  COUNT(*)
FROM
  `my_bq_project.my_bq_dataset.my_table`
WHERE LOWER('AAA') IN (
  SELECT LOWER(some_text)
  FROM UNNEST(array_1) a, UNNEST(a.array_2)
)