# JSON Parsing in Oracle SQL and PL/SQL

As an Oracle APEX developer, I regularly consume JSON from external web services. When I can, I use [APEX REST Data Sources](https://docs.oracle.com/en/database/oracle/apex/22.1/htmdb/creating-a-REST-data-source.html) or the [APEX_DATA_PARSER](https://blog.cloudnueva.com/apexdataparser) 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](https://objectstorage.us-ashburn-1.oraclecloud.com/n/nueva/b/nueva-public/o/Blog%20Public%20Files%2Fjson_parsing%2Fsample.json) 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_UTC_TIMESTAMP_TZ.html) 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](https://cdn.hashnode.com/res/hashnode/image/upload/v1660166721633/VKdH06Q2U.png align="left")

# 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/json-types.html#GUID-639D871E-D116-4793-888E-F7948E48F4DE) 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](https://blog.cloudnueva.com/for-speeds-sake-stop-using-apexjson)
- 🩳 [APEX Shorts](https://blog.cloudnueva.com/series/apex-shorts)
- #️⃣ [APEX Posts](https://blog.cloudnueva.com/tag/orclapex)
- 📖 [Oracle Documentation JSON Developers Guide](https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/index.html)
- 📖 [Oracle Documentation JSON Data Structures](https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/json-types.html#GUID-BDE10AAA-445B-47B5-8A39-D86C8EA99283)