JSON support was introduced in Oracle starting from version 12. This article provides an example SQL query for processing an API JSON response containing statuses and errors. The query demonstrates how to extract data from nested structures, preserve the order of statuses, and aggregate error messages.

This is an example of processing a real API message from a transportation company, which contains the result of a courier request. The task is to retrieve and save the current processing status of the request and any error messages (if present).

Example JSON Structure

{
  "entity": {
    "uuid": "7dc5aa3f-e441-4c37-95c0-6927304e42e9",
    "cdek_number": "101954699434",
    "from_location": {
        "code": 15326,
        "city_uuid": "331dd291-d809-4eff-83ff-417ac1e59def",
        "city": "Inskoy"
    },
    "to_location": {
        "code": 450,
        "city_uuid": "27f58584-4034-4a6c-810b-daa741a2d075",
        "city": "Petrozavodsk"
    },
    "need_call": true,
    "statuses": [
        {
            "code": "READY_FOR_APPOINTMENT",
            "name": "Ready for Appointment",
            "date_time": "2025-12-05T13:23:15+0000"
        },
        {
            "code": "INVALID",
            "name": "Invalid Request",
            "date_time": "2025-12-05T13:23:15+0000"
        },
        {
            "code": "ACCEPTED",
            "name": "Accepted",
            "date_time": "2025-12-05T13:23:15+0000"
        }
    ],
    "requests": [
        {
            "type": "CREATE",
            "date_time": "2025-12-05T13:23:15+0000",
            "state": "INVALID",
            "errors": [
                {
                    "code": "v2_internal_error",
                    "message": "Internal Error"
                }
            ]
        }
    ]
  }
}

SQL Query for JSON Processing

WITH all_parsed_data AS (
  SELECT 
    jt.*
  FROM JSON_TABLE(
    '{...}', '$'
    COLUMNS (
      entity_uuid VARCHAR2(100) PATH '$.entity.uuid',
      NESTED PATH '$.entity.statuses[*]' COLUMNS (
        status_order     FOR ORDINALITY,
        status_code      VARCHAR2(100) PATH '$.code',
        status_name      VARCHAR2(400) PATH '$.name',
        status_date_time VARCHAR2(100) PATH '$.date_time'
      ),
      NESTED PATH '$.entity.requests[*]' COLUMNS (
        request_type VARCHAR2(100) PATH '$.type',
        req_state    VARCHAR2(100) PATH '$.state',
        NESTED PATH '$.errors[*]' COLUMNS (
          err_code VARCHAR2(200) PATH '$.code',
          err_msg  VARCHAR2(4000) PATH '$.message'
        )
      )
    )
  ) jt
)
SELECT 
  s.entity_uuid,
  s.status_code,
  s.status_name,
  TO_TIMESTAMP_TZ(
    REPLACE(REGEXP_REPLACE(s.status_date_time,'([+-]\d{2})(\d{2})$','\1:\2'),'T',' '),
    'YYYY-MM-DD HH24:MI:SS TZH:TZM'
  ) - NUMTODSINTERVAL(s.status_order, 'SECOND') as status_date_time,
  e.errors_text
FROM (
  -- Unique statuses
  SELECT DISTINCT 
    entity_uuid,
    status_order,
    status_code,
    status_name,
    status_date_time
  FROM all_parsed_data
  WHERE status_code IS NOT NULL
) s
LEFT JOIN (
  -- Aggregated errors
  SELECT 
    req_state,
    LISTAGG(err_code || ': ' || err_msg, '; ') WITHIN GROUP (ORDER BY err_code) as errors_text
  FROM all_parsed_data
  WHERE err_code IS NOT NULL
  GROUP BY req_state
) e ON s.status_code = e.req_state
ORDER BY s.status_order;

Query Explanation

This SQL query performs the following tasks:

  1. Extracting Data from JSON: The JSON_TABLE function is used to transform the nested JSON structure into a tabular format.
  2. Preserving Status Order: The status_order field preserves the order of elements in the statuses array, as all date_time values are identical.
  3. Aggregating Errors: Errors from the errors array are combined into a single string using LISTAGG.
  4. Processing Timestamps: The status_date_time field is converted to the TIMESTAMP WITH TIME ZONE format, accounting for the timezone.

Advantages of the Approach

  • Flexibility: Enables working with nested JSON structures and extracting data at various levels of nesting.
  • Order Preservation: The use of FOR ORDINALITY ensures the order of elements in the array is maintained.
  • Data Aggregation: Allows combining data from different parts of the JSON (e.g., statuses and errors).
  • Versatility: Suitable for processing complex JSON structures with nested arrays and objects.

Additional Notes

  • Optimization: For large JSON structures, consider using indexes on columns involved in filtering.
  • Error Handling: Ensure the JSON data is valid to avoid parsing errors.