Skip to content
Advertisement

Filtering on an array field in BigQuery

I have the following query to get data for a movie:

enter image description here

And to filter by those Records where Genres includes: “Drama”:

enter image description here

However, how would I filter by those records that have a genre of Drama but only include those genres I specify (Drama). In other words, I want the result set to look like:

And Not include all genres within the record — I only want those matching genres — how would I do that?


Update: after some futzing around, here is what worked for me — it seems incredibly hack-ish and not really sure why it works (an answer explaining how to better do this would be great), but here it is:

Update 2: simplified to:

Advertisement

Answer

Try joining with UNNEST:

enter image description here

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement