How to Add Multiple JSON_BUILD_OBJECT entries to a JSON_AGG

Question

I am using PostgreSQL 9.4 and I have a requirement to have an 'addresses' array which contains closed JSON objects for different types of address (residential and correspondence). The structure should look like this:

[
  {
    "addresses": [
      {
        "addressLine1": "string",
        "type": "residential"
      },
      {
        "addressLine1": "string",
        "type": "correspondence"
      }
    ],
    "lastName": "string"
  }
]

...and here's some example data to illustrate the desired result:

[
    {
        "addresses": [
            {
                "addressLine1": "54 ASHFIELD PADDOCK",
                "type": "residential"
            },
            {
                "addressLine1": "135 MERRION HILL",
                "type": "correspondence"
            }
        ],
        "lastName": "WRIGHT"
    },
    {
        "addresses": [
            {
                "addressLine1": "13 BOAKES GROVE",
                "type": "residential"
            },
            {
                "addressLine1": "46 BEACONSFIELD GRANGE",
                "type": "correspondence"
            }
        ],
        "lastName": "DOHERTY"
    }
]

This is where I've gotten to with my SQL:

SELECT
    json_agg(
    json_build_object('addresses',(SELECT json_agg(json_build_object('addressLine1',c2.address_line_1,
                                                                     'type',c2.address_type
                                                                    )
                                                  )
                                   FROM my_customer_table c2
                                   WHERE c2.person_id=c.person_id
                                  ),
                     'addresses',(SELECT json_agg(json_build_object('addressLine1',c2.corr_address_line_1,
                                                                    'type',c2.corr_address_type
                                                                   )
                                                 )
                                  FROM my_customer_table c2
                                  WHERE c2.person_id=c.person_id
                                 ),
                     'lastName',c.surname
                            )
        ) AS customer_json
FROM
    my_customer_table c
WHERE
    c.corr_address_type IS NOT NULL /*exclude customers without correspondence addresses*/

...and this runs, however it repeats the 'addresses' object twice and has an array for each address variant, not around the overall array.

What I stupidly thought would work, is the following:

SELECT
    json_agg(
    json_build_object('addresses',(SELECT json_agg(json_build_object('addressLine1',c2.address_line_1,
                                                                     'type',c2.address_type
                                                                    ),
                                                   json_build_object('addressLine1',c2.corr_address_line_1,
                                                                     'type',c2.corr_address_type
                                                                    )
                                                  )
                                             FROM my_customer_table c2
                                             WHERE c2.person_id=c.person_id
                                  ),
                      'lastName',c.surname
                     )
          ) AS customer_json
FROM
    my_customer_table c
WHERE
    c.corr_address_type IS NOT NULL /*exclude customers without correspondence addresses*/

...however this throws an error:

"ERROR: function json_agg(json, json) does not exist. LINE 3: json_build_object('addresses',(SELECT json_agg(json_build_o... HINT: No function matches the given name and argument types. You might need to add explicit type casts."

I've Googled this, however no posts found seem to relate to the same kind of result I'm trying to get.

Does anyone know if it's possible to have multiple JSON_BUILD_OBJECT entries inside of an array?

Answer 1

A colleague has found a solution to this. It's totally different to the way I was approaching it, but works nicely. Here's the working code:

SELECT
    json_agg(subquery.customer_json) AS customer_json
FROM
    (
    SELECT
        row_to_json(t) AS customer_json
    FROM (
        SELECT
        (
          SELECT array_to_json(array_agg(addresses_union))
          FROM (
              SELECT
                    c.address_line_1 AS "addressLine1",
                    c.address_type as type
                UNION ALL
              SELECT
                    c.corr_address_Line_1 AS "addressLine1",
                    c.corr_address_type as type
              ) AS addresses_union
        ) as addresses,
            c.surname AS "lastName"
        FROM
            my_customer_table c
        WHERE
            c.corr_address_type IS NOT NULL /*exclude customers without correspondence address*/
    ) t
    ) subquery