google-bigquery - 如何使用UNNEST或任何其他函数展平数组？

Question

假设我得到一个带有一个字段的临时表是一个数组，如何将其转换为多行？

使用PostgreSQL可以使用UNNEST http://sqlfiddle.com/#!15/21673/19完成

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

SELECT UNNEST(arr) FROM x

在BigQuery中运行相同的查询说语法错误:意外
关键字UNNEST位于[3:8]

在BigQuery中，UNNEST似乎只能放在FROM子句之后，

然后我尝试了这些:

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

SELECT * FROM UNNEST(x)

这个人说UNNEST不能应用于表格:x在[3:22]；或这个

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

SELECT * FROM UNNEST(x.arr)

说UNNEST不能应用于表:x.arr at [3:22]

顺便说一句，当前的临时表x看起来像这样:

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

SELECT * FROM x
EOF

+--------------+
|     row      |
+--------------+
| [u'1', u'2'] |
+--------------+

而我希望将其转化为值(value)行:

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

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

Answer 1

另一个版本-涉及“显式” UNNEST

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