Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Sample code to parse JSON in Oracle PL/SQL using JSON_OBJECT_T
DECLARE
lc_json_file_url CONSTANT VARCHAR2(500) := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/nueva/b/nueva-public/o/Blog%20Public%20Files%2Fjson_parsing%2Fsample.json';
l_json CLOB;
l_orders_obj JSON_OBJECT_T;
l_orders_arr JSON_ARRAY_T;
l_order_obj JSON_OBJECT_T;
l_lines_arr JSON_ARRAY_T;
l_order_date TIMESTAMP WITH TIME ZONE;
l_ship_date TIMESTAMP WITH TIME ZONE;
l_keys JSON_KEY_LIST;
l_keys_str VARCHAR2(32000);
-- Function to convert an ISO 8601 Date String (e.g. "2022-08-04T08:00:06.000Z"
-- to an Oracle TIMESTAMP WITH TIME ZONE data type. This is only needed because
-- Oracle does not provide a PL/SQL equivelent for TO_UTC_TIMESTAMP_TZ
FUNCTION convert_iso_date (p_date IN VARCHAR2) RETURN TIMESTAMP WITH TIME ZONE IS
l_date TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT TO_UTC_TIMESTAMP_TZ (p_date) INTO l_date
FROM sys.dual;
RETURN l_date;
END convert_iso_date;
BEGIN
-- Get the JSON Document from OCI.
l_json := apex_web_service.make_rest_request
(p_url => lc_json_file_url,
p_http_method => 'GET');
-- Parse the JSON into a JSON Object Type.
l_orders_obj := json_object_t.parse(l_json);
-- Disable error handling (the default).
l_orders_obj.On_error(0);
-- Get the numeric value for 'order_count' from the 'meta' object
dbms_output.put_line('Value of [order_count] in the [meta] object: ' ||
l_orders_obj.get_Object('meta').get_Number('order_count'));
-- Get the 'orders' array from the JSON
l_orders_arr := l_orders_obj.get_Array('orders');
dbms_output.put_line('Count of rows in the [orders] array: '||
l_orders_arr.get_size);
-- Loop Through 'orders' array.
-- *** Note: The Loop Index for JSON_OBJECT starts at 0 NOT 1 ***
FOR i IN 0..l_orders_arr.get_size -1 LOOP
-- Get the order object for the for the current order in the 'orders' array
l_order_obj := json_object_t(l_orders_arr.get(i));
dbms_output.put_line(' [order_number]: ' || l_order_obj.get_Number('order_number'));
-- Output the fields, objects and arrays directly accessible from the Order Object.
-- This could be useful if you do not always know what fields could be in the JSON.
l_keys := l_order_obj.get_keys;
FOR x IN 1..l_keys.COUNT LOOP
l_keys_str := l_keys_str || l_keys(x) || ',';
END LOOP;
dbms_output.put_line(' Order Keys: [' || l_keys_str || ']') ;
-- Convert the ISO8601 to TIMESTAMP WITH TIME ZONE assumes the time is in the UTC time zone.
l_order_date := convert_iso_date (p_date => l_order_obj.get_String('order_date'));
dbms_output.put_line(' [order_date]: '||
TO_CHAR(l_order_date, 'DD-MON-YYYY HH24:MI:SS TZH:TZM'));
-- Check if the optional field 'ship_date' exists in the order object.
IF l_order_obj.has('ship_date') THEN
l_ship_date := convert_iso_date (p_date => l_order_obj.get_String('ship_date'));
dbms_output.put_line(' [ship_date] : '||
TO_CHAR(l_ship_date, 'DD-MON-YYYY HH24:MI:SS TZH:TZM'));
ELSE
dbms_output.put_line(' [ship_date] : N/A');
END IF;
-- Output fields from the 'customer' object.
dbms_output.put_line(' [customer].[number]: '||
l_order_obj.get_Object('customer').get_Number('number'));
dbms_output.put_line(' [customer].[name] : '||
l_order_obj.get_Object('customer').get_String('name'));
-- Loop through the 'lines' array for the current order.
l_lines_arr := l_order_obj.get_Array('lines');
dbms_output.put_line(' Order Lines : ' || l_lines_arr.get_size);
FOR y IN 0..l_lines_arr.get_size -1 LOOP
-- Reference fields in the line without converting it to an object first.
dbms_output.put_line(' [line_number] : ' ||
json_object_t(l_lines_arr.get(y)).get_Number('line_number'));
dbms_output.put_line(' [item_number] : ' ||
json_object_t(l_lines_arr.get(y)).get_String('item_number'));
END LOOP; -- End Lines Loop
END LOOP; -- End Orders Loop
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment