Extracting Attendant Information from JSON in Oracle Using JSON_VALUE and JSON_TABLE

In recent database management tasks, I encountered the need to extract an “attendant” ID from a JSON document stored in a CLOB column within the table df_nsf_mov_det. This information was crucial for storing the value in another table. The challenge lay in the fact that the “response” field contained a complex JSON structure, as shown below:

{
    "id": 1234567890,
    "name": "John Good Client",
    "first_interaction_at": "2023-10-11T08:16:43-03:00",
    "last_interaction_at": "2023-10-11T10:06:37-03:00",
    "perception": "warm",
    "status": "scheduled",
    "conversion_goal": "LEAD",
    "flow": "web",
    "contact": {
        ...
    },
    "attendant": {
        "id": 34089,
        "name": "Mary Good Attendant"
    },
    "location": {
        ...
    },
    ...
}

In this scenario, I turned to Oracle Database’s JSON capabilities, specifically the functions JSON_VALUE and JSON_TABLE, to extract the “attendant” ID. Let’s explore how these two Oracle features were employed:

Using JSON_VALUE

JSON_VALUE is a function in Oracle that allows you to extract a specific value from a JSON document. It is particularly handy when you need to retrieve a single, specific piece of data from the JSON, such as a numeric identifier.

Example:
To extract the “attendant” ID (which is 34089 in this case) using JSON_VALUE, the following SQL query was applied:

SELECT JSON_VALUE(response, '$.attendant.id' RETURNING NUMBER) AS attendant_id
FROM df_nsf_mov_det;

In this query, the JSON_VALUE function is used to access the “attendant” -> “id” field in the JSON, and the result is returned as attendant_id.

Utilizing JSON_TABLE

JSON_TABLE, on the other hand, is a feature that enables you to transform JSON data into a virtual table. This approach is beneficial when you need to extract multiple values from a JSON document or convert the JSON structure into a tabular format.

Example:
To extract both the “attendant.id” and “attendant.name” fields from the JSON structure, a SQL query similar to the one below was employed:

SELECT j.attendant_id, j.attendant_name
FROM df_nsf_mov_det d,
     JSON_TABLE(d.response, '$'
       COLUMNS (
         attendant_id NUMBER PATH '$.attendant.id',
         attendant_name VARCHAR2(255) PATH '$.attendant.name'
       )
     ) j;

In this query, JSON_TABLE is used to parse the JSON, creating a virtual table j with columns for attendant_id and attendant_name. The PATH clause is utilized to specify the location of the desired fields within the JSON structure.

Choosing the Right Approach

Selecting between JSON_VALUE and JSON_TABLE depends on your specific requirements. If you need to retrieve a single, specific value, JSON_VALUE is the direct choice. In contrast, if you seek to extract multiple fields or convert JSON data into a tabular format, JSON_TABLE is the appropriate approach. Both methods are powerful tools for handling JSON data in an Oracle database, and the choice relies on the complexity of your data and querying needs.

In conclusion, Oracle Database equips you with versatile tools to work with JSON data efficiently. Whether you opt for JSON_VALUE or JSON_TABLE depends on the nature of your JSON data and your unique use case. Familiarity with these methods will empower you to navigate the world of JSON data within Oracle Database.