如何在PostgreSQL中从单个JSON数组字段生成多行数据

要实现将JSON数组的每个元素拆分为单独行，且适配任意数量的数组元素，你可以通过展开数组并按索引关联的方式来实现。以下是修正后的完整方案：

1. 简化数据导入（可选优化） #

你原本导入coolant_consum的SQL可以简化，无需先转为文本再转回JSONB，直接提取JSONB字段即可：

INSERT INTO coolant_consum(coolant_stock_kg,coolant_disposed_kg,coolant)
select j->'coolant_stock_kg',
       j->'coolant_disposed_kg',
       j->'coolant'
from raw_json cross join jsonb_array_elements(data) as j;

2. 生成多行数据的核心SQL #

通过jsonb_array_elements WITH ORDINALITY展开每个数组并获取元素的索引位置，再按索引将三个字段的对应元素关联：

WITH expanded_stock AS (
    SELECT 
        elem::text AS coolant_stock,
        ordinality AS idx
    FROM coolant_consum,
         jsonb_array_elements(coolant_stock_kg) WITH ORDINALITY AS arr(elem, ordinality)
),
expanded_disposed AS (
    SELECT 
        elem::text AS coolant_disposed,
        ordinality AS idx
    FROM coolant_consum,
         jsonb_array_elements(coolant_disposed_kg) WITH ORDINALITY AS arr(elem, ordinality)
),
expanded_coolant AS (
    SELECT 
        elem::text AS coolant,
        ordinality AS idx
    FROM coolant_consum,
         jsonb_array_elements(coolant) WITH ORDINALITY AS arr(elem, ordinality)
)
SELECT 
    COALESCE(es.coolant_stock, '') AS coolant_stock,
    COALESCE(ed.coolant_disposed, '') AS coolant_disposed,
    ec.coolant
FROM expanded_coolant ec
LEFT JOIN expanded_stock es ON ec.idx = es.idx
LEFT JOIN expanded_disposed ed ON ec.idx = ed.idx
ORDER BY ec.idx;

执行结果 #

针对你的测试数据，上述SQL会返回3行（对应coolant数组的长度），结果如下：

coolant_stock | coolant_disposed | coolant
--------------|------------------|--------
3             | 3                | R1
7.4           | 7.4              | R2
              |                  | R2

方案说明 #

• jsonb_array_elements WITH ORDINALITY：展开JSON数组的同时，返回每个元素的序号（从1开始），用于关联不同数组的对应位置元素。
• LEFT JOIN：确保即使不同数组长度不一致，也能保留所有元素（短数组没有对应索引的元素会显示为空字符串）。
• 若你要求三个数组必须长度一致，可将LEFT JOIN替换为INNER JOIN，仅返回三个数组都有对应元素的行。

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