how to set a condition for arrays in where clause [BigQuery]

Question

I have an array in my table - something like this:

I need to take into account only rows where 'top_authors.author' = 'Caivi" and 'top_authors.total_score' = 3

I was trying to use unnest function but still I get the error "No matching signature for operator = for argument types: ARRAY, STRING. Supported signatures: ANY = ANY"

Could you help mi with that?

note: (just in case) and 'top_authors.total_score' = 3 - this part of WHERE clause makes no much sense! — Mikhail Berlyant
I know :) but In my case I have to write two conditions in "where" clause :) but ofc you're right :D in the above case this part of WHERE makes no sense. I couldnt find a better example online :) — monika_w091
don't look for examples online to include and reference in your question as this totally obfuscates your real use-case - Instead just present your case as is so we will be able to help you! — Mikhail Berlyant

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-04-29T14:18:22

You can unnest() in a subquery in the where clause:

where exists (select 1
              from unnest(top_authors) ta
              where ta.author = 'Caivi' and ta.total_score = 3
             )

Or you can do this in the main query:

select . . . 
from t cross join
     unnest(top_authors) ta
where ta.author = 'Caivi' and ta.total_score = 3;

Assuming you don't have duplicates in the array, these should produce equivalent results.

how to set a condition for arrays in where clause [BigQuery]

1 Answers