UNNEST 操作符用于将 ARRAY 类型字段展开为多行记录。根据数组元素类型不同，可分为展开普通数组和 STRUCT 数组两种场景。UNNEST 可独立使用，也可与 JOIN 语法组合，实现对表中数组列的横向展开。
UNNEST语法展开数组
UNNEST 操作符用于将 ARRAY 数组展开，返回一个表，每行对应数组中的一个元素。
命令格式
SELECT ... FROM UNNEST (<expression>) [AS <alias>];参数说明
expression：必填。待展开的表达式。ARRAY 类型。
alias：可选。指定展开后列的别名。若省略，系统将自动生成默认列名（如 __generated_unnest_col_c0）。
使用示例
使用alias指定展开后列的别名。
SELECT * FROM unnest(array(1,2,3));
-- 返回结果
+---------------------------+
| __generated_unnest_col_c0 |
+---------------------------+
| 1 |
| 2 |
| 3 |
+---------------------------+
SELECT * FROM unnest(array(1,2,3)) t;
-- 返回结果
+------+
| t |
+------+
| 1 |
| 2 |
| 3 |
+------+UNNEST语法展开STRUCT数组
UNNEST 操作符作用于 STRUCT 数组（数组中的每一个元素都是STRUCT类型）时，UNNEST 语法会展开 STRUCT 类型的每一个元素。若 STRUCT 中嵌套了其他 STRUCT 类型，则只能展开 STRUCT 的最上一层的元素，不会展开更深层次的元素。
命令格式
SELECT ... FROM UNNEST (<expression>) [AS <alias>];参数说明
expression：必填。待展开的表达式。STRUCT 数组，即 ARRAY<STRUCT> 类型。
alias：可选。指定展开后 STRUCT 列的别名。可通过 <alias>.field 引用字段，也可直接使用字段名。
使用示例
示例 1：使用 alias 指定展开后 STRUCT 元素的别名
SELECT * FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b)));
-- 返回结果
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
SELECT *, t FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b))) t;
-- 返回结果
+------+------+------------+
| a | b | t |
+------+------+------------+
| 1 | 2 | {a:1, b:2} |
| 3 | 4 | {a:3, b:4} |
+------+------+------------+示例 2：通过设置的别名来访问里面的元素，也可以通过 <alias>.field 访问STRUCT的元素。
SELECT a, t.a FROM UNNEST(ARRAY(STRUCT(1 AS a, 2 AS b), STRUCT(3 AS a, 4 AS b))) t;
-- 返回结果
+------+------+
| a | a_1 |
+------+------+
| 1 | 1 |
| 3 | 3 |
+------+------+示例 3：UNNEST 语法只能展开 STRUCT 的最上一层的元素，不会展开更深层次的元素。比如对于 STRUCT 内部还有一个 STRUCT 类型的值，并不会把内层的 STRUCT 展开。
SELECT * FROM UNNEST(ARRAY(STRUCT(STRUCT(1 AS a1, 2 AS a2) AS a, 3 AS b), STRUCT(STRUCT(4 AS a1, 5 AS a2) AS a, 6 AS b))) t;
-- 返回结果
+--------------+------+
| a | b |
+--------------+------+
| {a1:1, a2:2} | 3 |
| {a1:4, a2:5} | 6 |
+--------------+------+UNNEST语法在JOIN语法的使用方法
UNNEST 语法和 JOIN 语法同样作为 FROM 语法的子句，可以组合使用。当 UNNEST 语法作为 JOIN 语法的右表时，UNNEST 语法可以指定 JOIN 语法左表的某一列（ARRAY类型）进行展开。
命令格式
SELECT ... FROM ... JOIN UNNEST (<expression>) [AS <alias>] [ON <join_condition>]参数说明
expression：必填。JOIN 左表中 ARRAY 类型的列。
alias：可选。指定展开后的元素的别名。
join_condition：可选。JOIN 连接条件，是一个或多个等式表达式组合。格式为on equality_expression [and equality_expression]...，equality_expression为等式表达式。
使用示例
示例 1：UNNEST 指定的 JOIN 左表的列必须是 ARRAY 类型，可以通过设置 alias 来指代 ARRAY 类型的每一个元素。
WITH t AS (SELECT * FROM VALUES (ARRAY(1,2,3)) t(a))
SELECT * FROM t JOIN UNNEST(t.a);
-- 返回结果
+-----------+---------------------------+
| a | __generated_unnest_col_c0 |
+-----------+---------------------------+
| [1, 2, 3] | 1 |
| [1, 2, 3] | 2 |
| [1, 2, 3] | 3 |
+-----------+---------------------------+
WITH t AS (SELECT * FROM VALUES (ARRAY(1,2,3)) t(a))
SELECT * FROM t JOIN UNNEST(t.a) col;
-- 返回结果
+-----------+------+
| a | col |
+-----------+------+
| [1, 2, 3] | 1 |
| [1, 2, 3] | 2 |
| [1, 2, 3] | 3 |
+-----------+------+示例 2：UNNEST 指定的 JOIN 左表的列，其 ARRAY 内部的元素类型也可以是 STRUCT 类型，此时 UNNEST 语法会展开 STRUCT 中的每一个字段。
WITH t AS (SELECT * FROM VALUES (ARRAY(STRUCT(1 AS f1, 2 AS f2), STRUCT(3 AS f1, 4 AS f2))) t(a))
SELECT * FROM t JOIN UNNEST(t.a);
-- 返回结果
+------------------------------+------+------+
| a | f1 | f2 |
+------------------------------+------+------+
| [{f1:1, f2:2}, {f1:3, f2:4}] | 1 | 2 |
| [{f1:1, f2:2}, {f1:3, f2:4}] | 3 | 4 |
+------------------------------+------+------+
WITH t AS (SELECT * FROM VALUES (ARRAY(STRUCT(1 AS f1, 2 AS f2), STRUCT(3 AS f1, 4 AS f2))) t(a))
SELECT *, col.f1 FROM t JOIN UNNEST(t.a) col;
-- 返回结果
+------------------------------+------+------+------+
| a | f1 | f2 | f1_1 |
+------------------------------+------+------+------+
| [{f1:1, f2:2}, {f1:3, f2:4}] | 1 | 2 | 1 |
| [{f1:1, f2:2}, {f1:3, f2:4}] | 3 | 4 | 3 |
+------------------------------+------+------+------+示例 3：使用 ON 等值条件进行过滤
WITH user_configs AS (
SELECT
'user_001' AS user_id,
2 AS current_dept_id,
ARRAY(1, 2, 3) AS allowed_dept_ids
UNION ALL
SELECT
'user_002' AS user_id,
4 AS current_dept_id,
ARRAY(4, 5) AS allowed_dept_ids
)
SELECT
u.user_id,
u.current_dept_id,
dept_id AS allowed_dept_id
FROM user_configs u
JOIN UNNEST(u.allowed_dept_ids) AS dept_id
ON dept_id = u.current_dept_id;
-- 返回结果
+----------+-----------------+-----------------+
| user_id | current_dept_id | allowed_dept_id |
+----------+-----------------+-----------------+
| user_001 | 2 | 2 |
| user_002 | 4 | 4 |
+----------+-----------------+-----------------+