I have a following JSON data in a CLOB variable. Let's call it as "p_clob".
{
"CustomerTransactionId": 300000164734650,
"DueDate": "2023-07-31",
"ConversionDate": null,
"ConversionRate": null,
"receivablesInvoiceLines": [
{
"LineNumber": 1,
"Description": "TEST DESCRIPTION1",
"Quantity": 10,
"UnitSellingPrice": 1100,
"TaxClassificationCode": null,
"SalesOrder": null
},
{
"LineNumber": 2,
"Description": "TEST DESCRIPTION2",
"Quantity": 10,
"UnitSellingPrice": 1100,
"TaxClassificationCode": null,
"SalesOrder": null
},
{
"LineNumber": 3,
"Description": "TEST DESCRIPTION3",
"Quantity": 10,
"UnitSellingPrice": 1100,
"TaxClassificationCode": null,
"SalesOrder": null
},
{
"LineNumber": 4,
"Description": "TEST DESCRIPTION4",
"Quantity": 10,
"UnitSellingPrice": 1100,
"TaxClassificationCode": null,
"SalesOrder": null
}
]
}
The array "receivablesInvoiceLines" has 4 lines.
I need to insert total of 4 rows in a table using a select below.
INSERT INTO dump_Data
SELECT
*
FROM
JSON_TABLE ( p_clob
COLUMNS
customertransactionid VARCHAR2 ( 300 ) PATH '$.CustomerTransactionId',
duedate VARCHAR2 ( 50 ) PATH '$.DueDate',
conversiondate VARCHAR2 ( 20 ) PATH '$.ConversionDate',
conversionrate NUMBER PATH '$.InvoiceCurrencyCode',
specialinstructions NUMBER PATH '$.SpecialInstructions',
crossreference VARCHAR2 ( 50 ) PATH '$.CrossReference',
documentnumber VARCHAR2 ( 20 ) PATH '$.DocumentNumber',
transactionnumber NUMBER PATH '$.TransactionNumber',
transactiondate DATE PATH '$.TransactionDate',
);
COMMIT;
How can I structure the receivableInvoiecLines section in above select and insert 4 rows in the table?
You can use nesting for this, eg
SQL> create table orders (orders_json BLOB, check (orders_json is JSON));
Table created.
SQL>
SQL> insert into orders values ('
2 {
3 "orderNumber": 421234,
4 "customerName": "Jane Doe",
5 "date": "2020-08-17T10:15:01",
6 "orderItems": [{
7 "itemNo": 1234,
8 "name": "Coffee Machine",
9 "quantity": "1",
10 "unitPrice": 299.99
11 }, {
12 "itemNo": 3456,
13 "name": "Coffee Beans",
14 "quantity": "12",
15 "unitPrice": 19.50
16 },
17 {
18 "itemNo": 7654,
19 "name": "Coffee Cup",
20 "quantity": "2",
21 "unitPrice": 9.99
22 }
23 ]
24 }');
1 row created.
SQL>
SQL> select jt.*
2 from orders,
3 JSON_TABLE (orders_json, '$.orderItems[*]' COLUMNS (
4 ITEM_NO number path '$.itemNo',
5 NAME varchar2(40) path '$.name',
6 QUANTITY number path '$.quantity',
7 UNIT_PRICE number(5,2) path '$.unitPrice'
8 )) jt;
ITEM_NO NAME QUANTITY UNIT_PRICE
---------- -------------------- -------- ----------
1234 Coffee Machine 1 299.99
3456 Coffee Beans 12 19.5
7654 Coffee Cup 2 9.99
SQL> select jt.*
2 from orders,
3 JSON_TABLE (orders_json, '$' COLUMNS (
4 ORDER_NUMBER number path '$.orderNumber',
5 CUST_NAME varchar2(40) path '$.customerName',
6 ORDER_DATE date path '$.date',
7 NESTED PATH '$.orderItems[*]' COLUMNS(
8 ITEM_NO number path '$.itemNo',
9 NAME varchar2(40) path '$.name',
10 QUANTITY number path '$.quantity',
11 UNIT_PRICE number path '$.unitPrice'
12 )))jt;
ORDER_NUMBER CUST_NAME ORDER_DATE ITEM_NO NAME QUANTITY UNIT_PRICE
------------ ---------------- ---------------------- ---------- -------------------- -------- ----------
421234 Jane Doe 17-AUG-20 1234 Coffee Machine 1 299.99
421234 Jane Doe 17-AUG-20 3456 Coffee Beans 12 19.5
421234 Jane Doe 17-AUG-20 7654 Coffee Cup 2 9.99