Parsing CLOB containing nested JSON using Oracle's JSON_TABLE clause

Question

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?

Answer 1

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