How to flatten an array with UNNEST or any other functions?

Question

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

With PostgreSQL this can be done with 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 says Syntax error: Unexpected keyword UNNEST at [3:8]

it seems in BigQuery the UNNEST can only be put after FROM clause,

Then I have tried these:

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

SELECT * FROM UNNEST(x)

this one says UNNEST cannot be applied on a table: x at [3:22]; or this

WITH x AS (SELECT ARRAY[1,3,2] AS row)

SELECT * FROM UNNEST(x.arr)

says UNNEST cannot be applied on a table: x.arr at [3:22]

BTW, 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 am expecting to turn it into rows of value:

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

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

Answer 1

Yet another version - with "explicit" UNNEST involved

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