PostgreSQL中JsonB列多键数据转多行/合并行的SQL实现求助

假设你的表名为your_table，jsonb列名为jsonb_column，且该列存储的是标准JSON数组（示例中ID=1的多对象数据需以数组形式存储，如[{"code1": "AB", "code2": "BC", "code3": "CD"}, {"code1": "DE", "code2": "EF"}]，PostgreSQL的jsonb列无法直接存储多个独立JSON对象）。

格式一：拆分为多行 #

使用jsonb_array_elements将JSON数组展开为单行对象，提取对应字段值：

SELECT 
    t.id,
    j->>'code1' AS "Code 1",
    j->>'code2' AS "Code 2",
    j->>'code3' AS "Code 3"
FROM 
    your_table t,
    jsonb_array_elements(t.jsonb_column) j;

缺失的字段（如ID=1第二个对象的code3）会返回NULL，显示为空值。

格式二：同ID下字段值用~拼接 #

通过分组+string_agg函数聚合同ID的同名字段值：

SELECT 
    t.id,
    string_agg(j->>'code1', '~') AS "Code 1",
    string_agg(j->>'code2', '~') AS "Code 2",
    string_agg(j->>'code3', '~') AS "Code 3"
FROM 
    your_table t,
    jsonb_array_elements(t.jsonb_column) j
GROUP BY 
    t.id;

单个值的字段（如ID=1的code3）会直接显示原值，无需拼接。

兼容非数组格式的特殊处理 #

如果你的jsonb列存在单个JSON对象（而非数组）的情况，可先统一转换为数组再执行上述查询：

WITH formatted_data AS (
    SELECT 
        id,
        CASE 
            WHEN jsonb_typeof(jsonb_column) = 'object' THEN jsonb_build_array(jsonb_column)
            ELSE jsonb_column
        END AS jsonb_array
    FROM your_table
)
-- 此处替换为格式一或格式二的查询语句
SELECT 
    id,
    j->>'code1' AS "Code 1",
    j->>'code2' AS "Code 2",
    j->>'code3' AS "Code 3"
FROM formatted_data, jsonb_array_elements(jsonb_array) j;

内容的提问来源于stack exchange，提问作者Luis Enrique Garduno Morales