PostgreSQL嵌套JSON数据扁平化方法求助

修改后的SQL代码 #

SELECT 
    (datasets ->> 'shard')::int AS shard,
    (datasets ->> 'prefix')::int AS prefix,
    (datasets ->> 'id')::int AS id,
    (field_obj ->> 'timestamp')::bigint AS timestamp,
    (field_obj ->> 'amount')::int AS amount
FROM (
    SELECT 
        json_array_elements(json) AS datasets
    FROM (
        SELECT '[
            {
                "amounts": {
                    "fields": [
                        {
                            "amount": 11111,
                            "timestamp": "1703119840677243794"
                        },
                        {
                            "amount": 22222,
                            "timestamp": "1703206309696691698"
                        }
                    ]
                },
                "shard": 0,
                "prefix": 0,
                "id": 12345
            }
        ]'::json
    ) d
) c,
json_array_elements(datasets -> 'amounts' -> 'fields') AS field_obj;

问题原因说明 #

原SQL在同一个子查询里多次调用json_array_elements(fields)，数组会被多次独立展开，进而产生笛卡尔积——每个字段名和每个字段值两两配对，最终得到不符合预期的结果。

关键修改点 #

1. 将fields数组的展开操作移到FROM子句中（横向连接），确保每个数组元素只被展开一次，得到包含amount和timestamp的完整单个对象。
2. 直接从单个对象中提取目标字段，避免拆分键值对后重新配对的问题。
3. 使用->>代替->直接提取文本值，再按需转换为对应数据类型（int、bigint），让结果匹配预期的表格结构。

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