I have a need to generate a three level nested JSON payload out of Oracle tables. I have described two approaches I have used to accomplish this and neither of them work. Please advise where am I going wrong!
Approach 1
Following select is supposed to generate a single json clob containing header, line and detail line tables. You will see that I am trying to get header, lines and distribution lines payload but its not working. I am getting header rows repeating with every line rows. So, as a result when this payload goes into a webservice, because header row is repeating, webservice will throw an error. How can I make header row not repeat with every line row?
select json_object
(
KEY 'InvoiceNumber' VALUE h.document_id,
KEY 'InvoiceCurrency' VALUE 'USD',
KEY 'InvoiceAmount' VALUE h.amount,
KEY 'InvoiceDate' VALUE h.trx_date,
KEY 'BusinessUnit' VALUE 'ABCD Corp',
KEY 'Supplier' VALUE 'NASA',
KEY 'SupplierSite' VALUE 'PHYSICAL',
KEY 'InvoiceGroup' VALUE 'MoonLander',
KEY 'Description' VALUE 'Some Description',
KEY 'invoiceLines' VALUE json_array
(
json_object
( KEY 'LineNumber' VALUE t.line_id,
KEY 'LineAmount' VALUE t.line_Value,
KEY 'invoiceDistributions' VALUE json_array
(
json_object
(
KEY 'DistributionLineNumber' VALUE t.line_id,
KEY 'DistributionLineType' VALUE 'Item',
KEY 'DistributionAmount' VALUE t.line_Value
)
)
))FORMAT JSON
)JSON_VALUE
INTO aCLOB
from XXRR_HDR_STG h,
XXRR_LINE_STG t
where t.document_id = h.document_id
and h.document_id = 543210
order by t.line_id;
Approach 2
This approach uses Json Object and arrays. This is my preferable option but this gives Json Syntax error during runtime.
FOR i IN gethdrrec LOOP
-- Create JSON payload
l_json.put('InvoiceNumber',i.document_id);
l_json.put('InvoiceCurrency','USD');
l_json.put('InvoiceAmount',i.amount);
l_json.put('InvoiceDate', i.trx_date);
l_json.put('BusinessUnit', 'ABCD Corp');
l_json.put('Supplier', 'NASA');
l_json.put('SupplierSite','PHYSICAL');
l_json.put('InvoiceGroup','RR');
l_json.put('Description', 'Some Descr');
FOR j IN getlnrec(i.document_id) LOOP
l_children.append(json_object_t('
{
"LineNumber": "'|| j.line_id || '",
"LineAmount": "'|| j.line_value|| '",
}'));
l_grandchild.append(json_object_t('
{ "DistributionLineNumber": "'|| j.line_id || '",
"DistributionLineType": "Item",
"DistributionAmount":"'|| j.line_value|| '",
"DistributionCombination": "254.000.000.2111010.000.0.0"
}'
));
END LOOP;
END LOOP;
l_json.put('invoiceLines', l_children);
l_json.put('invoiceDistributions',l_grandchild);
envelope := l_json.to_clob;
Finally here is the example payload structure I am trying to generate using above methods
{
"InvoiceNumber": "MA_APInvoicex1",
"InvoiceCurrency": "USD",
"InvoiceAmount": 2212.75,
"InvoiceDate": "2023-07-07",
"BusinessUnit": "ABC Corp",
"Supplier": "NASA",
"SupplierSite": "Saint Paul",
"InvoiceGroup": "July2023",
"Description": "Office Supplies",
"invoiceLines": [{
"LineNumber": 1,
"LineAmount": 2112.75,
"invoiceDistributions": [{
"DistributionLineNumber": 1,
"DistributionLineType": "Item",
"DistributionAmount": 2112.75
}]
},
{
"LineNumber": 2,
"LineType": "Freight",
"LineAmount": 100,
"ProrateAcrossAllItemsFlag": true,
"invoiceDistributions": [{
"DistributionLineNumber": 2,
"DistributionLineType": "Item",
"DistributionAmount": 2112.75
}]
},{
"LineNumber": 3,
"LineType": "Freight",
"LineAmount": 100,
"ProrateAcrossAllItemsFlag": true,
"invoiceDistributions": [{
"DistributionLineNumber": 3,
"DistributionLineType": "Item",
"DistributionAmount": 2112.75
}]
}]
}
Here are tables to produce above query as requested by @MT0
CREATE TABLE xxrr_hdr_stg(document_id, amount, trx_date) AS
SELECT 543210, 100, SYSDATE FROM DUAL
CREATE TABLE xxrr_line_stg(document_id, line_id, line_value) AS
SELECT 543210, 1, 2112.75 FROM DUAL;
INSERT INTO xxrr_line_stg(document_id, line_id, line_value)
SELECT 543210, 2, 2112.75 FROM DUAL;
INSERT INTO xxrr_line_stg(document_id, line_id, line_value)
SELECT 543210, 3, 2112.75 FROM DUAL;
commit;
Please advise.
Thank you ,
Darsh
2
Answers
That first option works fine but a nested object needs its own subquery. Here is an example on the emp/dept sample dataset. It selects from dual since the outer level is a single key that has the all rows in an array, but that can also be from a table like you’re doing.
You need to use a
GROUP BY
clause when you have aggregated and non-aggregated columns. Given your structure you would want to use correlated sub-queries andJSON_ARRAY
for the inner-most object (rather thanJSON_ARRAYAGG
, since you only have a single object and are not aggregating anything) :Which, for the sample data:
Outputs:
fiddle