String Functions on Unnested Arrays in BigQuery SQL

Question

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

Mikhail Berlyant · Accepted Answer · 2021-12-29 21:15:49Z

2

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)
)

answered Dec 29, 2021 at 21:15

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mikhail Berlyant Over a year ago

glad it worked for you. consider also voting up the answer then :o)

Collectives™ on Stack Overflow

String Functions on Unnested Arrays in BigQuery SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related