JSON Parsing in Oracle SQL and PL/SQL
3 min read
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
- 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
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.
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.
The sample SELECT statement below selects all three levels of the JSON document in a flat structure.
- 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.
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.
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.
The sample code below parses the JSON, loops through the
orders array, and then the
lines array within that.
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;
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.