Simplest way to unnest a BQ table

I can directly unnest an array from a literal in BQ as follows:

select * from unnest([1,2,3])
# [{"f0_": "1"},{"f0_": "2"},{"f0_": "3"}]

How can I do the same from a with statement? For example, something like:

with Table as (
  select [1,2,3] as arr
 ) select * from unnest(Table.arr) ?? -- currently get unrecognized table name

What would be the correct syntax for the above? Here would be one example I came up with: is this the simplest way to do a general unnest?

with Movie as (
  select "Spider-Man" as Title, ['Sci-Fi', 'Action'] as Genres
 ) select Movie.Title, Genre from Movie cross join unnest(Movie.Genres) Genre

If so, why can’t the above be done without a cross join?

Answer

How can I do the same from a with statement?

with Table as (
  select [1,2,3] as arr
) 
select x from Table, unnest(arr) x

is this the simplest way to do a general unnest?

Yes!

Advertisement

Answer