如何使用PostgreSQL JSON函数打平深度嵌套的JSONB数据

关于FROM子句嵌套jsonb_array_elements的可行性 #

完全可以在FROM子句中嵌套调用jsonb_array_elements打平深度嵌套的JSONB类型列，这也是PostgreSQL处理嵌套JSON数组展开的标准方案，行为稳定可控，比在SELECT列表中放置返回集合的函数（SRF）可靠性高很多。

SELECT列表嵌SRF查询的执行逻辑 #

你提到的直接在SELECT中写两层jsonb_array_elements取语言、同时展开颜色数组的写法，不会执行隐式INNER JOIN，结果不符合预期的原因和执行逻辑如下：

• PostgreSQL对SELECT列表中放置多个SRF的处理逻辑存在特殊规则：10版本之前会按多个SRF返回结果的行位置对齐取值，长度较短的结果集会自动补NULL，不会生成全量笛卡尔积，这就是你拿不到所有语言和颜色全组合的根本原因；10版本后虽然调整了同级别SRF的处理逻辑改为生成笛卡尔积，但嵌套SRF、多SRF混合使用时依然容易出现非预期结果，不推荐使用这种写法。
• 这种写法下，SRF的计算是基于当前扫描到的基表行逐行执行的，没有独立的连接逻辑，结果完全依赖SRF的返回顺序和版本内置处理规则，可控性极差。

无需子查询的简洁实现方案 #

不需要写嵌套子查询，直接在FROM子句中逐层展开JSON数组即可——PostgreSQL中FROM子句后跟的SRF默认支持LATERAL引用，可以直接访问前面已经展开的字段，不管JSON嵌套多少层，顺着层级追加展开语句就行，逻辑清晰且不会出现结果偏差。
要实现全量语言、颜色组合+过滤英文的需求，代码如下：

SELECT lang ->> 'language' AS lang, color
FROM item_groups,
     -- 展开第一层items数组
     jsonb_array_elements(group_data -> 'items') AS item,
     -- 展开第二层每个item下的name数组
     jsonb_array_elements(item -> 'name') AS lang,
     -- 展开颜色数组
     jsonb_array_elements_text(group_data -> 'colors') AS color
WHERE lang ->> 'language' = 'en'
ORDER BY lang, color;

这个写法的优势：

• 无冗余子查询，不会出现子查询重复扫描表、表内有多行数据时结果串扰的问题
• 所有展开操作默认走隐式CROSS JOIN LATERAL，会正确生成所有维度的全量笛卡尔组合，不会漏数
• 嵌套层级再深也只需要按JSON结构追加一行jsonb_array_elements展开即可，不会出现子查询嵌套过深的可读性问题
• 过滤条件可以直接写在WHERE子句中，不需要套层后再过滤

内容的提问来源于stack exchange，提问作者Samuli Asmala