Sample code showing how to parse JSON using JSON_TABLE

JSON_Parsing_Using_JSON_TABLE.sql

SELECT jt.order_count
,      jt.order_number
,      jt.status
-- TO_UTC_TIMESTAMP_TZ converts an ISO 8601 Date and 
--  Time String to a TIMESTAMP WITH TIME ZONE data type.
,      TO_UTC_TIMESTAMP_TZ (jt.order_date)  order_date
,      TO_UTC_TIMESTAMP_TZ (jt.ship_date)   ship_date
,      jt.customer_number
,      jt.customer_name
,      jt.line_number
,      jt.item_number
-- apex_web_service.make_rest_request fetches the sample JSON from OCI Object Storage
--   JSON_TABLE is then working on the JSON returned from OCI Object Storage
FROM   JSON_TABLE(apex_web_service.make_rest_request
                   (p_url => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/nueva/b/nueva-public/o/Blog%20Public%20Files%2Fjson_parsing%2Fsample.json',
                    p_http_method => 'GET'), '$' ERROR ON ERROR
                      -- The ERROR ON ERROR clause will raise an error if something is wrong.
                      -- For example, if one of the fields is missing from the JSON document.
         -- Level 1 JSON Object, Get 'meta' object information.
         COLUMNS (order_count       NUMBER       PATH meta.order_count,
           -- Level 1 'orders' Array.
           NESTED PATH '$.orders[*]'
             COLUMNS (order_number      NUMBER       PATH order_number,
                      status            VARCHAR2(50) PATH status,
                      order_date        VARCHAR2(50) PATH order_date,
                      -- NULL ON ERROR will default a specific field to NULL if an error is encountered
                      --   ship_date is optional, so it defaults to NULL if the field is not found.
                      --   This would not be necessary if ERROR ON ERROR was not set above.
                      ship_date         VARCHAR2(50) PATH ship_date NULL ON ERROR,
                      customer_number   NUMBER       PATH customer.number,
                      customer_name     VARCHAR2(50) PATH customer.name,
               -- Level 2 'lines' Array, nested within 'orders' array.
               NESTED PATH '$.lines[*]'
                COLUMNS (line_number    NUMBER       PATH line_number,
                         item_number    VARCHAR2(50) PATH item_number)
       ))) jt
-- In the WHERE and ORDER BY clauses, I am just illustrating that you can
--  perform all the usual SQL operations on the virtual table generated by JSON_TABLE
WHERE jt.customer_number IN (120,200)
ORDER BY jt.order_number, jt.line_number;