As an Oracle APEX developer, I regularly consume JSON from external web services. When I can, I use APEX REST Data Sources or the APEX_DATA_PARSER PL/SQL API to turn JSON into rows of data that can be easily consumed by APEX. There are many occasions, however, where you need to parse and consume JSON directly from SQL or PL/SQL.
If you are like me, you learn best by following examples. In this post, I will use a sample JSON document and demonstrate two methods for parsing it:
- From a SQL statement using JSON_TABLE
- From a PL/SQL program using JSON_OBJECT_T
Note:
- The sample JSON and sample code are designed to illustrate as many different aspects of JSON parsing as possible
- I have included comments in the sample code to explain important aspects
Sample JSON
I uploaded the sample JSON to OCI Object Storage to easily access it from my sample code. I suggest you download it as it will be helpful when reviewing the rest of this post.
JSON_TABLE
This method is well suited for parsing JSON documents with a primary array that you need to loop through and does not require complex calculations or calls out to other PL/SQL functions and procedures. This method is faster than JSON_OBECT_T.
Sample Code
The sample SELECT statement below selects all three levels of the JSON document in a flat structure.
Notes:
- TO_UTC_TIMESTAMP_TZ is a useful SQL function that converts ISO 8601 date or date time strings into an Oracle TIMESTAMP WITH TIME ZONE type.
Sample Result
The result is a 'flat' representation of the JSON where meta.order_count is repeated for every order and line, and order information is repeated for every order line.
JSON_OBJECT_T
This method is better suited for parsing more complex JSON documents where you may need to iterate through multiple arrays, call out to other PL/SQL functions and procedures, or perform complex logic on data in the JSON document.
Sample Code
The sample code below parses the JSON, loops through the orders array, and then the lines array within that.
Sample Result
The excerpt below shows the output from the above code for the first two orders only.
Value of [order_count] in the [meta] object: 4
Count of rows in the [orders] array: 4
[order_number]: 1001
Order Keys: [order_number,status,order_date,ship_date,customer,lines,]
[order_date]: 09-AUG-2022 16:47:06 +00:00
[ship_date] : 10-AUG-2022 16:47:06 +00:00
[customer].[number]: 100
[customer].[name] : ABC Incorporated
Order Lines : 2
[line_number] : 1
[item_number] : QR-2112
[line_number] : 2
[item_number] : QR-1000
[order_number]: 1002
Order Keys: [order_number,status,order_date,ship_date,customer,lines,order_number,order_date,status,customer,lines,]
[order_date]: 02-AUG-2022 16:47:06 +00:00
[ship_date] : N/A
[customer].[number]: 200
[customer].[name] : Important Co
Order Lines : 1
[line_number] : 1
[item_number] : R2-D2
Error Handling for JSON_OBJECT_T
Oracle allows you to enable error handling using the On_error function. See the 'Error Handling' section of the documentation for details.
In the example code above, you will notice that I disabled error handling for the orders object using l_orders_obj.On_error(0);. This is because you cannot code around missing fields if you enable error handling. For example, in the sample JSON, ship_date is not always present for an order. If I enable error handling, then the line IF l_order_obj.has('ship_date') THEN raises an error. In my opinion, the .has function should not raise an error in this case.
The following code will raise an error if the ship_date field is missing for an order.
l_orders_obj.On_error(4);
-- Check if the optional field 'ship_date' exists
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;
Conclusion
I hope you found this helpful; I regularly refer to this sample code, especially when I need to parse the JSON from a new web service.