PostgreSQL中JSONB_ARRAY_ELEMENTS展开逻辑及相关问题咨询
测试环境与查询语句
首先创建测试表：
CREATE TABLE test_jsonb_expand ( id SERIAL PRIMARY KEY, data JSONB NOT NULL );
插入示例数据：
INSERT INTO test_jsonb_expand (data) VALUES ('{"events": [{"id": 1, "type": "enter"}, {"id": 141, "type": "exit"}], "other_data": ["whatever#1"]}'), ('{"events": [{"id": 1, "type": "enter"}, {"id": 150, "type": "exit"}], "other_data": ["whatever#1", "whatever#2"]}'), ('{"events": [{"id": 1, "type": "enter"}, {"id": 300, "type": "exit"}], "other_data": ["whatever#1", "whatever#2", "whatever#3"]}');
执行的查询语句：
SELECT id, JSONB_ARRAY_ELEMENTS(data->'events')->>'id' AS event_id, JSONB_ARRAY_ELEMENTS(data->'events')->>'type' AS event_type, JSONB_ARRAY_ELEMENTS(data->'other_data')->>0 AS other_data FROM test_jsonb_expand;
问题1：结果为何按插入顺序排列？
PostgreSQL中，未指定ORDER BY子句时，结果顺序默认由数据存储顺序决定。你的测试表用SERIAL作为主键，插入行的主键值按顺序递增，无其他索引或排序干预时，数据库通常会按主键的插入顺序返回数据。同时JSONB_ARRAY_ELEMENTS展开数组时，会严格遵循JSON数组的元素顺序输出行，最终结果就呈现出插入时的顺序。
问题2：该行为是否可靠？还是仅为实现细节，大数据量下无法保证？
仅作为实现细节，完全不可靠。
PostgreSQL官方明确规定：查询未指定ORDER BY时，数据库可以任意顺序返回结果。小数据量、无并发写入、无数据清理（如VACUUM）场景下，结果可能看似符合插入顺序，但一旦数据发生移动（比如执行VACUUM FULL、创建索引、批量删除后插入新数据），存储顺序改变，返回结果的顺序也会随之变化。
此外，你在SELECT列表中多次调用集合返回函数的写法本身存在风险：多个集合返回函数共存时，PostgreSQL会做笛卡尔积展开，这种行为在PostgreSQL 10前后的处理逻辑有差异，属于不规范写法，极易产生意外结果。如果需要关联多个数组的展开，应该用LATERAL子query明确关联逻辑。
问题3：PostgreSQL如何处理这类查询的展开逻辑？
当SELECT列表中存在多个集合返回函数时，PostgreSQL会将它们视为独立集合，执行笛卡尔积操作：
- 对每一行源数据，分别展开每个集合返回函数对应的数组；
- 将这些展开后的结果做笛卡尔积组合，生成最终输出行。
以你的测试数据为例：第一行events数组有2个元素，other_data数组有1个元素，生成2×1=2行；第二行events2个元素，other_data2个元素，生成2×2=4行；第三行events2个元素，other_data3个元素，生成2×3=6行，总计12行结果。
注意：PostgreSQL 10及以后版本中，若多个集合返回函数来自同一行的同一数组（比如你两次调用JSONB_ARRAY_ELEMENTS(data->'events')），数据库会尝试按元素位置索引对齐展开而非笛卡尔积，但这仍属于实现细节，不建议依赖。
问题4：是否有相关语法的官方文档？
PostgreSQL官方文档中有对应说明，核心相关部分包括：
- 集合返回函数（Set-Returning Functions）：明确这类函数的行为、在SELECT列表中的使用限制与处理逻辑；
- JSON函数和操作符：详细介绍
JSONB_ARRAY_ELEMENTS等JSON处理函数的用法与返回值； - LATERAL子查询：这是关联多个集合返回函数的规范写法，能明确控制展开逻辑，避免意外结果。
内容的提问来源于stack exchange，提问作者winwin
