跳到主要内容
版本: 最新版本-3.5

unnest

UNNEST 是一个表函数，它接受一个数组，并将该数组中的元素转换为表中的多行。这种转换也称为“扁平化”。

您可以将 Lateral Join 与 UNNEST 结合使用来实现常见的转换，例如从 STRING、ARRAY 或 BITMAP 到多行。有关更多信息，请参见Lateral join。

从 v2.5 开始，UNNEST 可以接受可变数量的数组参数。这些数组的类型和长度（元素数量）可以不同。如果数组的长度不同，则以最大长度为准，这意味着将向小于此长度的数组添加 null 值。有关更多信息，请参见示例 2。

从 v3.2.7 开始，UNNEST 可以与 LEFT JOIN ON TRUE 一起使用，即使右表中相应的行是空的或具有 null 值，也可以保留左表中的所有行。对于这些空行或 NULL 行，将返回 NULL。有关更多信息，请参见示例 3。

语法​

unnest(array0[, array1 ...])

参数​

array：要转换的数组。它必须是一个数组或可以计算为 ARRAY 数据类型的表达式。您可以指定一个或多个数组或数组表达式。

返回值​

返回从数组转换后的多行。返回值的类型取决于数组中元素的类型。

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

使用说明​

  • UNNEST 是一个表函数。它必须与 Lateral Join 一起使用，但不需要显式指定关键字 Lateral Join。
  • 如果数组表达式计算结果为 NULL 或为空，则不会返回任何行（LEFT JOIN ON TRUE 除外）。
  • 如果数组中的元素为 NULL，则为该元素返回 NULL。

示例​

示例 1：UNNEST 接受一个参数​

-- Create table student_score where scores is an ARRAY column.
CREATE TABLE student_score
(
    `id` bigint(20) NULL COMMENT "",
    `scores` ARRAY<int> NULL COMMENT ""
)
DUPLICATE KEY (id)
DISTRIBUTED BY HASH(`id`);

-- Insert data into this table.
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- Query data from this table.
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- Use UNNEST to flatten the scores column into multiple rows.
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 对应的 [80,85,87] 被转换为三行。

与 id = 2 对应的 [77,null,89] 保留 null 值。

与 id = 3 和 id = 4 对应的 scores 为 NULL 和空，因此被跳过。

示例 2：UNNEST 接受多个参数​

-- Create table example_table where the type and scores columns vary in type.
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 data into the table.
INSERT INTO example_table VALUES
("1", "typeA;typeB", [80,85,88]),
("2", "typeA;typeB;typeC", [87,90,95]);

-- Query data from the table.
SELECT * FROM example_table;
+------+-------------------+------------+
| id   | type              | scores     |
+------+-------------------+------------+
| 1    | typeA;typeB       | [80,85,88] |
| 2    | typeA;typeB;typeC | [87,90,95] |
+------+-------------------+------------+

-- Use UNNEST to convert type and scores into multiple rows.
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 列，而 scores 是一个 ARRAY 列。split() 函数用于将 type 转换为 ARRAY。

对于 id = 1，type 被转换为 ["typeA","typeB"]，它有两个元素。

对于 id = 2，type 被转换为 ["typeA","typeB","typeC"]，它有三个元素。

为了确保每个 id 的行数一致，将一个 null 元素添加到 ["typeA","typeB"]。

示例 3：带 LEFT JOIN ON TRUE 的 UNNEST​

-- Create table student_score where scores is an ARRAY column.
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 data into this table.
INSERT INTO student_score VALUES
(1, [80,85,87]),
(2, [77, null, 89]),
(3, null),
(4, []),
(5, [90,92]);

-- Query data from this table.
SELECT * FROM student_score ORDER BY id;
+------+--------------+
| id   | scores       |
+------+--------------+
|    1 | [80,85,87]   |
|    2 | [77,null,89] |
|    3 | NULL         |
|    4 | []           |
|    5 | [90,92]      |
+------+--------------+

-- Use 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 对应的 [80,85,87] 被转换为三行。

保留与 id = 2 对应的 [77,null,89] 中的 null 值。

与 id = 3 和 id = 4 对应的 scores 为 NULL 和空。Left Join 保留这两行，并为它们返回 NULL。