Skip to content

Instantly share code, notes, and snippets.

Last active September 4, 2022 23:35
Show Gist options
  • Save jon-dixon/dba1a17ea61335d13bbe52161cae1b35 to your computer and use it in GitHub Desktop.
Save jon-dixon/dba1a17ea61335d13bbe52161cae1b35 to your computer and use it in GitHub Desktop.
Sample code showing how to parse JSON using JSON_TABLE
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 => '',
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,
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment