PostgreSQL 9.4中如何在JSON_AGG内添加多个JSON_BUILD_OBJECT条目以生成多类型地址数组

Got it, let's get your JSON structure right! The issue with your original queries is either duplicate addresses keys or misusing json_agg (which only aggregates rows, not multiple direct arguments). Here are two straightforward ways to achieve your desired output:

Option 1: Use json_build_array (Simplest for Fixed Address Types) #

Since you're dealing with exactly two address types (residential and correspondence), you can use json_build_array to wrap both address objects directly into a single array. This is clean and efficient for your use case:

SELECT json_agg(
    json_build_object(
        'addresses', json_build_array(
            -- Residential address object
            json_build_object(
                'addressLine1', c.address_line_1,
                'type', c.address_type
            ),
            -- Correspondence address object
            json_build_object(
                'addressLine1', c.corr_address_line_1,
                'type', c.corr_address_type
            )
        ),
        'lastName', c.surname
    )
) AS customer_json
FROM my_customer_table c
WHERE c.corr_address_type IS NOT NULL; -- Exclude customers without correspondence addresses

This will generate exactly the structure you want: each entry has one addresses array containing both address objects, paired with the lastName.

Option 2: Use UNION ALL (Flexible for Dynamic Address Counts) #

If you ever need to handle more than two address types or dynamic address entries, you can use UNION ALL to combine address rows first, then aggregate them into an array:

SELECT json_agg(customer_data) AS customer_json
FROM (
    SELECT
        json_build_object(
            'addresses', (
                SELECT json_agg(address_obj)
                FROM (
                    -- Add residential address (if exists)
                    SELECT json_build_object(
                        'addressLine1', c.address_line_1,
                        'type', c.address_type
                    ) AS address_obj
                    WHERE c.address_type IS NOT NULL
                    UNION ALL
                    -- Add correspondence address (already filtered in outer query)
                    SELECT json_build_object(
                        'addressLine1', c.corr_address_line_1,
                        'type', c.corr_address_type
                    ) AS address_obj
                ) AS address_entries
            ),
            'lastName', c.surname
        ) AS customer_data
    FROM my_customer_table c
    WHERE c.corr_address_type IS NOT NULL
) AS customer_records;

Why Your Previous Queries Failed #

1. First Query: You were adding two separate addresses keys to json_build_object. While JSON allows duplicate keys (most parsers will keep the last one), this doesn't merge the arrays—it creates two separate addresses entries, which isn't what you wanted.
2. Second Query: json_agg is designed to aggregate rows from a query, not accept multiple JSON objects as direct arguments. That's why you got the "function does not exist" error—PostgreSQL has no json_agg(json, json) variant.

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