Filtering on an array field in BigQuery

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

with Movies as (
  select 'Titanic' as title,
  1997 as year, 
  ['Drama',' Romance'] as Genres
)
select * from Movies where title='Titanic'

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

with Movies as (
  select 'Titanic' as title,
  1997 as year, 
  ['Drama',' Romance'] as Genres
)
select * from Movies where "Drama" in UNNEST(Genres)

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:

title      year         Genres
Titanic    1997         Drama

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:

with Movies as (
  select 'Titanic' as title,
  1997 as year, 
  ['Drama',' Romance'] as Genres
)
select * from (
  select title, year, g from (select title, year, Genres as g from Movies t0, t0.Genres) group by title, year, g
) where g = 'Drama'

Update 2: simplified to:

with Movies as (
  select 'Titanic' as title,
  1997 as year, 
  ['Drama',' Romance'] as Genres
)
select title, year, Genres from Movies t0, t0.Genres where Genres='Drama'

Answer

Try joining with UNNEST:

with Movies as (
  select 'Titanic' as title, 1997 as year, ['Drama',' Romance'] as Genres
)
select title, year, Genre 
from Movies, UNNEST(Genres) as Genre
where Genre = "Drama"

Advertisement

Answer