PostgreSQL查询构建指定JSON结构报错求助

我需要编写PostgreSQL查询，基于给定的示例数据生成指定JSON结构：以p_id为主键，将同一人员的邮箱地址整合为数组email_details，地址和关联类型整合为数组personAddresses，但当前查询运行报错。

当前查询语句 #

SELECT json_agg(json_build_object(
    'p_id', p_id,
    'first_name', first_name,
    'email_details', json_agg(json_build_object(
        'email_address', email_add,
        'email_type', email_type
    )),
    'personAddresses', json_agg(json_build_object(
        'pp_code', pp_code,
        'rel_type', COALESCE(rel_type, rel_cd), -- 用COALESCE处理rel_type或rel_cd
        'addr1', addr_1
    ))
)) AS json_data
FROM (
    SELECT 
        p_id,
        first_name,
        pp_code,
        rel_type,
        rel_cd,
        addr_1,
        email_add,
        email_type
    FROM (
        SELECT '1' as p_id, 'purnima' as first_name, 1234 as pp_code, 'primary' as rel_type, null as rel_cd, '37/26' as addr_1, 'p.bhatia@test.com' as email_add, 'work' as email_type
        UNION ALL 
        SELECT '1' as p_id, 'purnima' as first_name, 6789 as pp_code, null as rel_type, 'other' as rel_cd, '99/44' as addr_1, 'p.bhatia@test12.com' as email_add, 'other' as email_type
        UNION ALL
        SELECT '2' as p_id, 'bhanu' as first_name, 3333 as pp_code, 'primary' as rel_type, null as rel_cd, '37/26' as addr_1, 'bhanu.bhatia@test.com' as email_add, 'work' as email_type
        UNION ALL
        SELECT '2' as p_id, 'bhanu' as first_name, 4444 as pp_code, null as rel_type, 'other' as rel_cd, '99/44' as addr_1, 'bhanu.bhatia@test12.com' as email_add, 'other222' as email_type
    ) sub
) subquery
GROUP BY p_id, first_name;

期望JSON输出 #

[
    {
        "p_id": 1,
        "first_name": "purnima",
        "email_details": [
            {
                "email_address": "p.bhatia@test.com",
                "email_type": "work"
            },
            {
                "email_address": "p.bhatia@test12.com",
                "email_type": "other"
            }
        ],
        "personAddresses": [
            {
                "pp_code": 1234,
                "rel_type": "primary",
                "addr1": "37/26"
            },
            {
                "pp_code": 6789,
                "rel_type": "other",
                "addr1": "99/44"
            }
        ]
    },
    {
        "p_id": 2,
        "first_name": "bhanu",
        "email_details": [
            {
                "email_address": "bhanu.bhatia@test.com",
                "email_type": "work"
            },
            {
                "email_address": "bhanu.bhatia@test12.com",
                "email_type": "other222"
            }
        ],
        "personAddresses": [
            {
                "pp_code": 3333,
                "rel_type": "primary",
                "addr1": "37/26"
            },
            {
                "pp_code": 4444,
                "rel_type": "other",
                "addr1": "99/44"
            }
        ]
    }
]

错误原因及修正后的查询 #

原查询错误在于在同一个GROUP BY层级中嵌套使用了json_agg，内层的json_agg没有针对每个p_id做分组聚合，导致PostgreSQL无法正确处理嵌套聚合逻辑。

修正方案是先对每个p_id分组，分别聚合邮箱和地址数据，再将整个人的信息整合为JSON对象，最后用json_agg生成最终数组：

SELECT json_agg(person_data) AS json_data
FROM (
    SELECT 
        p_id::INT,
        first_name,
        -- 聚合邮箱信息
        json_agg(json_build_object(
            'email_address', email_add,
            'email_type', email_type
        )) AS email_details,
        -- 聚合地址信息
        json_agg(json_build_object(
            'pp_code', pp_code,
            'rel_type', COALESCE(rel_type, rel_cd),
            'addr1', addr_1
        )) AS personAddresses
    FROM (
        SELECT '1' as p_id, 'purnima' as first_name, 1234 as pp_code, 'primary' as rel_type, null as rel_cd, '37/26' as addr_1, 'p.bhatia@test.com' as email_add, 'work' as email_type
        UNION ALL 
        SELECT '1' as p_id, 'purnima' as first_name, 6789 as pp_code, null as rel_type, 'other' as rel_cd, '99/44' as addr_1, 'p.bhatia@test12.com' as email_add, 'other' as email_type
        UNION ALL
        SELECT '2' as p_id, 'bhanu' as first_name, 3333 as pp_code, 'primary' as rel_type, null as rel_cd, '37/26' as addr_1, 'bhanu.bhatia@test.com' as email_add, 'work' as email_type
        UNION ALL
        SELECT '2' as p_id, 'bhanu' as first_name, 4444 as pp_code, null as rel_type, 'other' as rel_cd, '99/44' as addr_1, 'bhanu.bhatia@test12.com' as email_add, 'other222' as email_type
    ) sub
    GROUP BY p_id, first_name
) person_data;

说明 #

1. 内层子查询先按p_id和first_name分组，分别用json_agg聚合邮箱和地址数组，生成每个人员的完整数据对象；
2. 外层再用json_agg将所有人员对象组合成最终的JSON数组；
3. 额外将p_id转换为INT类型，匹配期望输出中的数值格式。

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