CODEWITHSUNDEEP
sqlgoogle-bigquery
David542
David542

Reputation: 110502

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'

enter image description here

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)

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:

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'

Upvotes: 1

Views: 3310

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

In cases when number of columns is more than just few - below version can be useful too

select * except(Genres)
from Movies, unnest(Genres) as Genre
where Genre = "Drama"

with output

enter image description here

In case if you (for some reason) need to preserve column name as Genres vs. Genre - below should work

select * except(Genres, Genre), Genre as Genres
from Movies, unnest(Genres) as Genre
where Genre = "Drama"

with output

enter image description here

Upvotes: 2

Sergey Geron
Sergey Geron

Reputation: 10222

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"

enter image description here

Upvotes: 1

Related Questions