How to smooth an array using UNNEST or any other functions?

Question

Suppose I get a temporary table with one field, this is an array, how to turn it into multiple rows?

With PostgreSQL, this can be done using UNNEST http://sqlfiddle.com/#!15/21673/19

WITH x AS (SELECT ARRAY[1,3,2] AS arr) SELECT UNNEST(arr) FROM x 

Run the same query in BigQuery. Syntax error: Unexpected UNNEST keyword in [3: 8]

it seems that in BigQuery UNNEST can only be delivered after a FROM clause,

Then I tried these:

 WITH x AS (SELECT ARRAY[1,3,2] AS arr) SELECT * FROM UNNEST(x) 

this one says that UNNEST cannot be applied to a table: x in [3:22]; or that

 WITH x AS (SELECT ARRAY[1,3,2] AS row) SELECT * FROM UNNEST(x.arr) 

says that UNNEST cannot be applied to a table: x.arr in [3:22]

BTW, the current temporary table x looks like this:

 WITH x AS (SELECT ARRAY[1,2] AS row) SELECT * FROM x EOF +--------------+ | row | +--------------+ | [u'1', u'2'] | +--------------+ 

while I expect to turn it into value strings:

 +-----+ | row | +-----+ | 1 | | 2 | +-----+ 

https://cloud.google.com/bigquery/sql-reference/arrays

Answer 1

Another version - with "explicit" participation of UNNEST

 WITH x AS (SELECT ARRAY[1,3,2] AS arr) SELECT arr_item FROM x, UNNEST(arr) as arr_item 

Answer 2

You can do this smoothing by doing CROSS JOIN of the arr elements with each row x, i.e.

 WITH x AS (SELECT ARRAY[1,3,2] AS arr) SELECT arr FROM x, x.arr 

or you can write it more explicitly as CROSS JOIN instead of using a comma

 WITH x AS (SELECT ARRAY[1,3,2] AS arr) SELECT arr FROM x CROSS JOIN x.arr