PostgreSQL 12.5 JSONB列异构嵌套数组查询及输出值提取方案
实现方案
通过PostgreSQL递归CTE遍历JSONB全路径,适配对象、数组混合嵌套的异构结构,自动提取outputs分支下的所有标量数值,输出name、value两列的结构化结果。
注:以下语句假设
Data_Source表中存储示例JSON的JSONB字段名为json_content,实际使用时替换为你的真实字段名即可,语句完全兼容PostgreSQL 12.5的内置JSONB能力,无需额外安装扩展。
WITH RECURSIVE json_full_walk AS ( -- 锚点:从每条记录的C1数组元素里,定位到outputs对象作为遍历起点 SELECT NULL::TEXT AS attr_name, elem -> 'outputs' AS node FROM Data_Source, jsonb_array_elements(json_content -> 'C1') AS elem UNION ALL -- 递归处理对象节点:拆分所有键值对,记录键名作为当前层的属性名 SELECT item.key AS attr_name, item.value AS node FROM json_full_walk, jsonb_each(node) AS item WHERE jsonb_typeof(node) = 'object' UNION ALL -- 递归处理数组节点:逐元素拆分,继承上层的属性名 SELECT json_full_walk.attr_name, arr_item AS node FROM json_full_walk, jsonb_array_elements(node) AS arr_item WHERE jsonb_typeof(node) = 'array' ) SELECT attr_name AS name, node::BIGINT AS value FROM json_full_walk WHERE jsonb_typeof(node) = 'number' AND attr_name IS NOT NULL ORDER BY -- 对齐期望输出顺序,无固定排序需求可删除该排序规则 CASE attr_name WHEN 'output_A1' THEN 1 WHEN 'output_A2' THEN 2 WHEN 'output_A2_1' THEN 3 WHEN 'output_A2_2' THEN 4 WHEN 'output_A2_3' THEN 5 WHEN 'output_A2_4' THEN 6 WHEN 'output_A2_5' THEN 7 END, value;
适配说明
- 递归逻辑自动适配任意层级的嵌套结构,不需要针对固定JSON路径硬编码取值逻辑,后续JSON结构调整层级也不需要修改核心语句。
- 完全匹配示例中的异构数组结构:针对
output_A2对应的「多层数组嵌套、数组内混合标量+对象」的特殊结构,遍历过程会自动将数组外层属性名匹配到数组首个标量值,同时将嵌套对象内的键值对拆分为独立行;对于output_A2_2对应的多元素数组,会自动展开为多个同name、不同value的行,和期望输出的结构完全一致。 - 示例JSON中
output_A1的实际存储值为403097,你给出的期望输出中403096为输入层inputs下的数值,如果存在跨字段的特殊映射规则,只需要在最终结果集上做关联替换即可,不影响整体提取逻辑的通用性。
内容的提问来源于stack exchange,提问作者papa.kostas
相关产品推荐
相关产品推荐

