StarRocks UNNEST 表函数完全指南数组/字符串/位图展开为多行的原理与实战【免费下载链接】starrocksThe worlds fastest open query engine for sub-second analytics both on and off the data lakehouse. With the flexibility to support nearly any scenario, StarRocks provides best-in-class performance for multi-dimensional analytics, real-time analytics, and ad-hoc queries. A Linux Foundation project.项目地址: https://gitcode.com/GitHub_Trending/st/starrocksUNNEST 是 StarRocks 内置的表函数Table Function它将一个 ARRAY 数组中的元素逐行展开flattening常与 Lateral Join 搭配实现行转列column-to-row转换是 ETL 处理中处理嵌套数据、用户标签数组、A/B 测试结果集等场景的高频工具。本文以官方文档为核心结合 StarRocks 前后端源码BE 端unnest.h/multi_unnest.h、FE 端QueryAnalyzer.java与单元测试完整讲解 UNNEST 的语法、参数、返回值、边界行为以及单参数、多参数、LEFT JOIN ON TRUE 三大实战场景帮助你直接复制运行并理解其底层执行原理。一、UNNEST 是什么UNNEST 是一个表函数它接收一个数组并将数组中的每个元素转换为结果表中的一行这种转换在数据库领域被称为「展平」flattening。在 StarRocks 中UNNEST 最常见的用法是与 Lateral Join 搭配实现将 STRING、ARRAY、BITMAP 等类型的数据展开为多行。例如一行记录中某个字段存储了[80, 85, 87]这样的数组经过 UNNEST 展开后会变成三行记录。更完整的 Lateral Join 用法可参考 Lateral Join 行转列指南。从版本演进看UNNEST 的能力在不断增强v2.5 起UNNEST 支持接收可变数量的数组参数且各数组的类型和长度元素个数可以不同。当数组长度不一致时以最大长度为准长度不足的数组会用 NULL 补齐见示例二。v3.2.7 起UNNEST 可以与LEFT JOIN ON TRUE搭配使用即使右表对应的行是空数组或 NULL也会保留左表的所有行并为这些空行返回 NULL见示例三。二、语法与参数语法unnest(array0[, array1 ...])参数说明参数说明array要转换的数组。必须是 ARRAY 数据类型或可求值得到 ARRAY 数据类型的表达式。可以指定一个或多个数组/数组表达式。返回值返回由数组转换得到的多行数据返回值的数据类型取决于数组元素的类型。ARRAY 支持的合法元素类型BOOLEAN、TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、VARCHAR、CHAR、DATETIME、DATE、JSON、ARRAY、MAP、STRUCT 等可参考 ARRAY 数据类型文档。三、使用注意事项UNNEST 是表函数必须与 Lateral Join 搭配使用但Lateral Join关键字不需要显式写出。如果数组表达式求值为 NULL 或为空数组则不返回任何行LEFT JOIN ON TRUE场景除外。如果数组中的某个元素为 NULL则该元素对应返回 NULL。四、实战示例示例一UNNEST 接收单个参数这是最基础的用法将 ARRAY 列展开为多行。-- 创建 student_score 表scores 为 ARRAY 列。 CREATE TABLE student_score ( id bigint(20) NULL COMMENT , scores ARRAYint 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] | -------------------- -- 使用 UNNEST 将 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对应的[80,85,87]被展开为三行。id 2对应的[77,null,89]中保留了 NULL 元素元素级 NULL 被保留返回一行 NULL。id 3和id 4的scores分别为 NULL 和空数组这两行被跳过不返回任何行。示例二UNNEST 接收多个参数v2.5 起支持。当多个数组的类型和长度不同时以最大长度为基准较短数组缺失的位置用 NULL 补齐。-- 创建 example_tabletype 和 scores 列类型不同。 CREATE TABLE example_table ( id varchar(65533) NULL COMMENT , type varchar(65533) NULL COMMENT , scores ARRAYint NULL COMMENT ) ENGINEOLAP 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 列scores是 ARRAY 列示例中用split()函数将type先转换为数组。id 1的type被转换为[typeA,typeB]共 2 个元素id 2的type被转换为[typeA,typeB,typeC]共 3 个元素。为保证每个id展开的行数一致[typeA,typeB]会被补上一个 NULL 元素因此结果中出现了id 1且type NULL的行。提示如果需要对多个列分别执行 unnest必须为每次 unnest 操作指定别名例如select v1, t1.unnest as v2, t2.unnest as v3 from lateral_test, unnest(v2) t1, unnest(v3) t2;详见 Lateral Join 指南。示例三UNNEST 与 LEFT JOIN ON TRUEv3.2.7 起支持。使用LEFT JOIN ON TRUE时即使右表UNNEST 展开结果为空或全为 NULL左表的所有行也都会被保留空行对应返回 NULL。-- 创建 student_score 表scores 为 ARRAY 列。 CREATE TABLE student_score ( id bigint(20) NULL COMMENT , scores ARRAYint 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对应的[80,85,87]被展开为三行。id 2对应的[77,null,89]保留了 NULL 元素。id 3和id 4的scores分别为 NULL 和空数组与示例一中被直接跳过不同Left Join 保留了这两行并为它们返回 NULL。对比示例一与示例三可以清晰看到LEFT JOIN ON TRUE的作用普通JOIN隐式 Cross/Lateral Join会丢弃空结果行而 Left Join 会以 NULL 补齐保证左表行不丢失。五、源码级原理UNNEST 在 StarRocks 中如何执行5.1 函数注册支持哪些元素类型UNNEST 是内置表函数在 BE 端的 table_function_factory.cpp 中注册。单参数版Unnest针对 ARRAY 元素类型做了全面的注册覆盖TINYINT、SMALLINT、INT、BIGINT、LARGEINT、FLOAT、DOUBLE、DECIMALV2、DECIMAL32/64/128、CHAR、VARCHAR、DATE、DATETIME、BOOLEAN以及嵌套的ARRAY、STRUCT、MAP、JSON、VARIANT等复合类型多参数版MultiUnnest则以{}任意参数形式注册用于兼容不同数量、不同类型的输入参数组合。从注册表可以看出UNNEST 不仅能展开基础类型的数组还能展开嵌套数组、STRUCT、MAP、JSON 等复杂类型数组这在半结构化数据分析如 JSON 数组、变体列中非常有用。5.2 核心执行逻辑Unnest 与 MultiUnnest单参数版的核心实现在 unnest.h 的Unnest::process()方法中。其核心思路是读取输入列的ArrayColumn视图利用数组列内置的offsets偏移量数组与elements元素列完成展开——每个数组元素区间[offsets[i], offsets[i1])决定该行展开出的元素个数。当输入行本身为 NULL 或数组为空时默认行为是不产生任何行若设置了is_left_join标记则会追加一个 NULL 作为补齐结果。元素级的 NULL 会原样保留在输出列中。多参数版的核心实现在 multi_unnest.h 的MultiUnnest::process()方法中。它逐行扫描所有输入数组列先计算每行所有数组中元素个数的最大值max_length_array_size作为该行展开的行数基准对于长度不足的数组通过append_nulls(max_length_array_size - array_element_length)在末尾补齐 NULL对于该行为 NULL 的数组列则整体补max_length_array_size个 NULL输出端同时维护一个copy_count_column记录每行应复制的行数供上层算子TableFunctionOperator按行数做数据复制。这正解释了文档中「数组长度不同时以最大长度为准、不足部分补 NULL」的行为——它是 MultiUnnest 展开策略的必然结果。5.3 前端语义Lateral Join 与 LEFT JOIN ON TRUE 的解析FE 端在 QueryAnalyzer.java 中完成表函数的语义分析当 Join 的右侧是unnest表函数时会将其标记为 Lateral Join对于LEFT JOIN场景代码会检查 Join 条件是否为TRUEleft join unnest only support on true满足条件后将is_left_join标记透传给 BE 端的Unnest::init()见 unnest.h从而驱动上面 5.2 节中提到的 NULL 补齐逻辑。另外当多参数 UNNEST 未显式指定列名时返回列名统一命名为unnest与 PostgreSQL 行为保持一致。5.4 测试佐证BE 端提供了完整的单元测试来验证上述行为unnest_core_test.cpp直接构造Unnest表函数实例通过init → prepare → open → process → close生命周期执行process()校验展开后的列数据与每行复制次数copy counts覆盖普通展开与 left join 两种模式。multi_unnest_core_test.cpp验证多数组参数、长度不一致时的 NULL 补齐逻辑。table_function_operator_test.cpp在 Pipeline 执行框架层面验证表函数算子的整体执行。六、典型应用场景结合 Lateral Join 行转列指南UNNEST 的常见场景包括1. 将字符串按分隔符拆分为多行-- 使用 split() 先把字符串转为数组再 unnest 展开。 select v1, unnest from lateral_test2, unnest(split(v2, ,)) as unnest;2. 将 ARRAY 列展开为多行select v1, v2, unnest from lateral_test, unnest(v2) as unnest;3. 展开 Bitmap 数据配合unnest_bitmap表函数可将 Bitmap 中聚合的整数集合展开为多行常用于去重用户 ID 的明细回溯select v1, unnest_bitmap from lateral_test3, unnest_bitmap(v2) as unnest_bitmap;注意当前 Lateral Join 仅与unnest()及其变体配合实现行转列暂不支持子查询其他表函数和 UDTF 的支持仍在规划中。七、总结能力版本行为要点单参数展开早期版本数组展开为多行NULL/空数组跳过元素 NULL 保留多参数展开v2.5多数组可类型、长度不同以最长者为准短数组补 NULLLEFT JOIN ON TRUEv3.2.7空/NULL 数组对应的左表行被保留返回 NULLUNNEST 是 StarRocks 半结构化数据处理的关键函数它在 FE 端完成 Lateral Join 语义解析在 BE 端通过数组列 offsets 与 elements 的高效内存布局实现低开销展开配合split()等函数即可完成从字符串、ARRAY 到 BITMAP 的各类行转列任务。建议在实际使用前通过 unnest_core_test.cpp 中覆盖的边界场景NULL 数组、空数组、元素 NULL、left join建立对结果集形态的准确预期再投入到生产 ETL 或分析查询中。【免费下载链接】starrocksThe worlds fastest open query engine for sub-second analytics both on and off the data lakehouse. With the flexibility to support nearly any scenario, StarRocks provides best-in-class performance for multi-dimensional analytics, real-time analytics, and ad-hoc queries. A Linux Foundation project.项目地址: https://gitcode.com/GitHub_Trending/st/starrocks创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考