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:
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?
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(+)