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.

https://gist.github.com/jon-dixon/dba1a17ea61335d13bbe52161cae1b35

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_Parsing_Using_JSON_TABLE_Output

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.

https://gist.github.com/jon-dixon/e2b294c78c0433f1c780b8aea7ebf1b7

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.

🔗 Read More

🔗 For Speed's Sake, Stop Using APEX_JSON
🩳 APEX Shorts
#️⃣ APEX Posts
📖 Oracle Documentation JSON Developers Guide
📖 Oracle Documentation JSON Data Structures

JSON Parsing in Oracle SQL and PL/SQL

Comments (2)

Oracle APEX Shorts

Easy Date Handling in JavaScript with apex.date

More from this blog

Using an APEX AI Agent to Turn Purchase Orders into JSON

APEX AI Agent Handlers: Intercepting & Updating Requests and Responses

APEX AI Agent Logging with Request & Response Handlers

AI Agents Need Boundaries, Not Bigger Prompts

The anatomy of an APEX 26.1 APEXlang file

Sample JSON

JSON_TABLE

Sample Code

Sample Result

JSON_OBJECT_T

Sample Code

Sample Result

Error Handling for JSON_OBJECT_T

Conclusion

🔗 Read More

Command Palette

Comments (2)

Oracle APEX Shorts

Easy Date Handling in JavaScript with apex.date

More from this blog

Sample JSON

JSON_TABLE

Sample Code

Sample Result

JSON_OBJECT_T

Sample Code

Sample Result

Error Handling for JSON_OBJECT_T

Conclusion

🔗 Read More