unnest

UNNEST 是一种表函数 (table function)，用于将一个数组展开成多行。

您可以将 StarRocks 的 Lateral Join 与 UNNEST 功能结合使用，实现常见的列转行逻辑，比如展开 STRING，ARRAY，和 BITMAP 类型的数据。更多使用示例，参见 Lateral join。

从 2.5 版本开始，UNNEST 支持传入多个 array 参数，并且多个 array 的元素类型和长度（元素个数）可以不同。对于长度不同的情况，以最长数组的长度为基准，长度小于这个长度的数组使用 NULL 进行元素补充，参见 示例二。

从 3.2.7 版本开始，UNNEST 支持 LEFT JOIN ON TRUE，会保留左表中的所有行，即使右表的表达式没有返回任何行，会对右表相应的行用空值填充。参见 示例三。

语法

unnest(array0[, array1 ...])

参数说明

array：待转换的数组或者能转化成数组的表达式，必填。

返回值说明

返回数组展开后的多行数据。返回值的数据类型取决于数组中的元素类型。

有关 StarRocks 支持的数组元素类型，请参见 ARRAY。

注意事项

• UNNEST 必须与 lateral join 一起使用，但是 lateral join 关键字可以在查询中省略。
• 支持输入多个数组，数组的长度和类型可以不同。
• 如果输入的数组为 NULL 或 空，则计算时跳过（LEFT JOIN ON TRUE 除外）。
• 如果数组中的某个元素为 NULL，该元素对应的位置返回 NULL。

示例

示例一：UNNEST 接收一个参数

-- 创建表 student_score，其中 scores 为 ARRAY 类型的列。
CREATE TABLE student_score
(
    `id` bigint(20) NULL COMMENT "",
    `scores` ARRAY<int> NULL COMMENT ""
)
DUPLICATE KEY (id)
DISTRIBUTED BY HASH(`id`);

-- 向表插入数据。
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- 查询表中数据。
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- 将 scores 列中的数组元素展开成多行。
SELECT id, scores, unnest FROM student_score, unnest(scores) AS unnest;
+------+--------------+--------+
| id   | scores       | unnest |
+------+--------------+--------+
|    1 | [80,85,87]   |     80 |
|    1 | [80,85,87]   |     85 |
|    1 | [80,85,87]   |     87 |
|    2 | [77,null,89] |     77 |
|    2 | [77,null,89] |   NULL |
|    2 | [77,null,89] |     89 |
|    5 | [90,92]      |     90 |
|    5 | [90,92]      |     92 |
+------+--------------+--------+

可以看到对于 id = 1 的 scores 数组，根据元素个数拆成了 3 行。

id = 2的 scores 数组中包含 null 元素，对应位置返回 null。

id = 3 和 id = 4 的 scores 数组分别是 NULL 和 空，计算时跳过。

示例二：UNNEST 接收多个参数

-- 创建表。
CREATE TABLE example_table (
id varchar(65533) NULL COMMENT "",
type varchar(65533) NULL COMMENT "",
scores ARRAY<int> NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(id)
COMMENT "OLAP"
DISTRIBUTED BY HASH(id)
PROPERTIES (
"replication_num" = "3");

-- 向表插入数据。
INSERT INTO example_table VALUES
("1", "typeA;typeB", [80,85,88]),
("2", "typeA;typeB;typeC", [87,90,95]);

-- 查询表中数据。
SELECT * FROM example_table;
+------+-------------------+------------+
| id   | type              | scores     |
+------+-------------------+------------+
| 1    | typeA;typeB       | [80,85,88] |
| 2    | typeA;typeB;typeC | [87,90,95] |
+------+-------------------+------------+

-- 使用 UNNEST 将 type 和 scores 这两列中的元素展开为多行。
SELECT id, unnest.type, unnest.scores
FROM example_table, unnest(split(type, ";"), scores) AS unnest(type,scores);
+------+-------+--------+
| id   | type  | scores |
+------+-------+--------+
| 1    | typeA |     80 |
| 1    | typeB |     85 |
| 1    | NULL  |     88 |
| 2    | typeA |     87 |
| 2    | typeB |     90 |
| 2    | typeC |     95 |
+------+-------+--------+

UNNEST 函数中的 type 列和 scores 列数据类型不相同。

type 列为 VARCHAR 类型，计算过程中使用 split() 函数转为了 ARRAY 类型。

id = 1 的 type 转化后得到数组 ["typeA","typeB"]，包含 2 个元素；id = 2 的 type 转化后得到数组 ["typeA","typeB","typeC"]，包含 3 个元素。以数组最长长度 3 为基准，对 ["typeA","typeB"] 补充了 NULL。

示例三：UNNEST 支持 LEFT JOIN ON TRUE

-- 创建表。
CREATE TABLE student_score
(
`id` bigint(20) NULL COMMENT "",
`scores` ARRAY<int> NULL COMMENT ""
)
DUPLICATE KEY (id)
DISTRIBUTED BY HASH(`id`)
PROPERTIES (
"replication_num" = "1"
);

-- 向表插入数据。
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- 查询表中数据。
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- 使用 LEFT JOIN ON TRUE。
SELECT id, scores, unnest FROM student_score LEFT JOIN unnest(scores) AS unnest ON TRUE ORDER BY 1, 3;
+------+--------------+--------+
| id   | scores       | unnest |
+------+--------------+--------+
|  1   | [80,85,87]   |     80 |
|  1   | [80,85,87]   |     85 |
|  1   | [80,85,87]   |     87 |
|  2   | [77,null,89] |   NULL |
|  2   | [77,null,89] |     77 |
|  2   | [77,null,89] |     89 |
|  3   | NULL         |   NULL |
|  4   | []           |   NULL |
|  5   | [90,92]      |     90 |
|  5   | [90,92]      |     92 |
+------+--------------+--------+

可以看到对于 id = 1 的 scores 数组 [80,85,87]，根据元素个数拆成了 3 行。

id = 2 的 scores 数组 [77,null,89] 中包含 null 元素，对应位置返回 NULL。

id = 3 和 id = 4 的 scores 数组分别是 NULL 和 空，Left Join 计算时保留这两行，用 NULL 填充。