Is there a way to use a JSON array for multi-row data in Oracle SQL?

Question

Edit: @mathguy has already correctly pointed out that I need to use JSON_ARRAYAGG to have this correctly handle multi-row data. However, I still have an outstanding issue with the lastName object not returning speechmarks. Can anyone advise why this might be, from the SQL below? Many thanks.

I have a requirement to generate JSON to insert data for lots of customers via an API. This contains JSON arrays (one overarching array for all customers and an array for potential multiple addresses) and objects. The code I'm currently using produces this:

    [
    {
        "address": [
            {
                "addressLine1": "ALLIANCE & LEICESTER PLC",
                "addressLine2": "CUSTOMER SERVICES",
                "addressLine3": "CARLTON PARK",
                "region": "LEICESTERSHIRE",
                "city": "LEICESTER",
                "zip": "LE190AL",
                "type": "residential"
            },
            {
                "addressLine1": null,
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": null,
                "zip": null,
                "type": null
            }
        ],
        "firstName": "SIOBHAN",
        "lastName":TOWNSEND
    }
]
[
    {
        "address": [
            {
                "addressLine1": "VIA DE LOS POBLADOS 2",
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": "MADRID",
                "zip": "28033",
                "type": "residential"
            },
            {
                "addressLine1": null,
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": null,
                "zip": null,
                "type": null
            }
        ],
        "firstName": "HAYDEN",
        "lastName":THOMSON
    }
]
[
    {
        "address": [
            {
                "addressLine1": "VIA DE LOS POBLADOS 1",
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": "MADRID",
                "zip": "28034",
                "type": "residential"
            },
            {
                "addressLine1": null,
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": null,
                "zip": null,
                "type": null
            }
        ],
        "firstName": "MADISON",
        "lastName":FROST
    }
]

...however I need it to look like this:

[
    {
        "address": [
            {
                "addressLine1": "ALLIANCE & LEICESTER PLC",
                "addressLine2": "CUSTOMER SERVICES",
                "addressLine3": "CARLTON PARK",
                "region": "LEICESTERSHIRE",
                "city": "LEICESTER",
                "zip": "LE190AL",
                "type": "residential"
            },
            {
                "addressLine1": null,
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": null,
                "zip": null,
                "type": null
            }
        ],
        "firstName": "SIOBHAN",
        "lastName":  "TOWNSEND"
    },
    {
        "address": [
            {
                "addressLine1": "VIA DE LOS POBLADOS 2",
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": "MADRID",
                "zip": "28033",
                "type": "residential"
            },
            {
                "addressLine1": null,
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": null,
                "zip": null,
                "type": null
            }
        ],
        "firstName": "HAYDEN",
        "lastName":  "THOMSON"
    },
    {
        "address": [
            {
                "addressLine1": "VIA DE LOS POBLADOS 1",
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": "MADRID",
                "zip": "28034",
                "type": "residential"
            },
            {
                "addressLine1": null,
                "addressLine2": null,
                "addressLine3": null,
                "region": null,
                "city": null,
                "zip": null,
                "type": null
            }
        ],
        "firstName": "MADISON",
        "lastName":  "FROST"
    }
]

The key differences here being:

The overarching array should start at the beginning of the 1st record and finish at the end of the last record, rather than ending after every "row"
For some reason, the last object in the list (lastName) doesn't have its value correctly listed in speechmarks
A comma separator should appear between each customer/row

This is the SQL I have been running:

SELECT
    json_array(
    json_object('address' VALUE
        json_array(json_object('addressLine1'    VALUE address.line_1,
                               'addressLine2'    VALUE address.line_2,
                               'addressLine3'    VALUE address.line_3,
                               'region'          VALUE address.county,
                               'city'            VALUE address.town,
                               'zip'             VALUE address.postcode,
                               'type'            VALUE 'residential'),
                   json_object('addressLine1'    VALUE correspondence_address.line_1,
                               'addressLine2'    VALUE correspondence_address.line_2,
                               'addressLine3'    VALUE correspondence_address.line_3,
                               'region'          VALUE correspondence_address.county,
                               'city'            VALUE correspondence_address.town,
                               'zip'             VALUE correspondence_address.postcode,
                               'type'            VALUE case when person.correspondence_address_id is null then null else 'correspondence' end)
                   ),
                'firstName'          VALUE person.first_name,
                'lastName'           VALUE person.surname
     FORMAT JSON)
              )as customer_json
FROM
    person,
    address,
    address correspondence_address
WHERE
    person.address_id=address.id
    and person.correspondence_address_id=correspondence_address.id(+)

This is being run against an Oracle 19c database. Can anyone help me to troubleshoot if it's possible to set a JSON_ARRAY to wrap around the full dataset, rather than it ending and restarting after each customer record?

Read about JSON_ARRAYAGG. Your current query is not *aggregating* into JSON arrays. — , Feb 25 '22 at 14:55
Thank you! This was exactly what I needed :) Wrapping a JSON_ARRAYAGG around the whole thing has addressed the row aggregation issue (I wish I had thought of that keyword when Googling). Strangely, I still have a problem with the lastName object not being handled correctly (i.e. no quotes are generated around it). Do you have any idea why this might be? — SteveUK9799, Feb 25 '22 at 15:03
Why (re: your last question) - FORMAT JSON assumes the input is already formatted for JSON, including the double-quotes around strings. Your inputs aren't, so you shouldn't give that option. (You figured out the "how" already, here I am really talking about the "why".) — , Feb 25 '22 at 15:21
The why is because I'm guilty of copy and pasting some code without understanding its purpose :| I've been working with the Oracle SQL JSON functions for a week and a half and my starting point was this: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-1084A518-A44A-4654-A796-C1DD4D8EC2AA. I was foolishly under the impression that the FORMAT JSON trailer was necessary in all instances. D'oh! — SteveUK9799, Feb 25 '22 at 15:27

SteveUK9799 · Answer 1 · 2022-02-25T15:24:55.707

Credit to @mathguy for correctly pointing out that I needed to use JSON_ARRAYAGG in order to get the SQL to correctly handle multiple rows.

I have realised that I needed to remove the "FORMAT JSON" from the end as this trailed from the lastName variable and impacted the way it was returned. Removing this fixed my issue. Here is the working code:

SELECT
json_arrayagg(json_object('address' VALUE
    json_array(json_object('addressLine1'    VALUE address.line_1,
                           'addressLine2'    VALUE address.line_2,
                           'addressLine3'    VALUE address.line_3,
                           'region'          VALUE address.county,
                           'city'            VALUE address.town,
                           'zip'             VALUE address.postcode,
                           'type'            VALUE 'residential'),
               json_object('addressLine1'    VALUE correspondence_address.line_1,
                           'addressLine2'    VALUE correspondence_address.line_2,
                           'addressLine3'    VALUE correspondence_address.line_3,
                           'region'          VALUE correspondence_address.county,
                           'city'            VALUE correspondence_address.town,
                           'zip'             VALUE correspondence_address.postcode,
                           'type'            VALUE case when person.correspondence_address_id is null then null else 'correspondence' end)
               ),
            'firstName'          VALUE person.first_name,
            'lastName'           VALUE person.surname
          ))as customer_json

FROM person, address, address correspondence_address WHERE person.address_id=address.id and person.correspondence_address_id=correspondence_address.id(+)

It would be better to post a complete solution with the updated code as that would be more helpful to future people searching for solutions to the same question. — MT0, Feb 25 '22 at 15:23

Is there a way to use a JSON array for multi-row data in Oracle SQL?

1 Answers1