使用Oracle的JSON_TABLE子句解析包含嵌套JSON的CLOB

v2g6jxz6 于 2023-10-16 发布在 Oracle

关注(0)|答案(1)|浏览(159)

我在一个CLOB变量中有一个JSON数据。我们称之为“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
         }
         
           ]
}

数组“receivablesqualiceLines”有4行。
我需要插入一个表中使用下面的选择共4行。

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;

我如何在上面的表格中选择和插入4行来构造receivablepixiecLines部分？

oracle

来源：https://stackoverflow.com/questions/77005187/parsing-clob-containing-nested-json-using-oracles-json-table-clause

1条答案

按热度按时间

h7wcgrx31#

你可以使用嵌套，例如

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

赞(0）回复(0）举报 2023-10-16

我来回答

使用Oracle的JSON_TABLE子句解析包含嵌套JSON的CLOB

1条答案

相关问题

热门标签

最新问答