The Shift to Unstructured Data#

Historically, Oracle databases strictly stored highly normalized relational data. If an external system sent an invoice, it was processed by a Java or Python middleware layer, broken into pieces, and inserted into INVOICE_HEADER and INVOICE_DETAILS tables.

With the rise of REST APIs and document databases, Oracle adapted. Beginning in 12c, and perfected in 19c and 21c, Oracle can natively store, query, and generate JSON and XML. You can now store a raw API payload in a CLOB column, enforce that it is valid JSON, and query elements out of it seamlessly using SQL.

Parsing JSON (JSON_TABLE)#

Assume we have a table API_LOGS with a CLOB column payload containing:

{
  "order_id": 1001,
  "customer": "Alice",
  "items": [
    {"item_no": "A1", "qty": 2},
    {"item_no": "B2", "qty": 1}
  ]
}

If we want to query this like a relational table, we use JSON_TABLE. It maps JSON paths to SQL columns.

SELECT 
  j.order_id,
  j.customer,
  j.item_no,
  j.qty
FROM api_logs a,
JSON_TABLE(
  a.payload, '$' 
  COLUMNS (
    order_id NUMBER PATH '$.order_id',
    customer VARCHAR2(50) PATH '$.customer',
    NESTED PATH '$.items[*]' COLUMNS (
      item_no VARCHAR2(20) PATH '$.item_no',
      qty NUMBER PATH '$.qty'
    )
  )
) j;

The NESTED PATH command beautifully unnests the array, automatically performing a relational join between the header elements (order_id) and the array elements!

Generating JSON#

Going the other direction, you can convert relational tables into complex, nested JSON objects using JSON_OBJECT and JSON_ARRAYAGG.

SELECT JSON_OBJECT(
  'department_id' VALUE d.dept,
  'department_name' VALUE d.dept_name,
  'employees' VALUE JSON_ARRAYAGG(
    JSON_OBJECT('id' VALUE e.emp_id, 'name' VALUE e.emp_name)
  )
) as json_payload
FROM dept_table d
JOIN emp_table e ON d.dept = e.dept
GROUP BY d.dept, d.dept_name;

This outputs perfectly formatted JSON directly from the database, ready to be sent to a REST API.

Parsing XML (XMLTABLE)#

While JSON is the modern standard, enterprise systems (like Oracle RMS and SOA Suite) still heavily rely on XML (SOAP, RIB messages).

The equivalent to JSON_TABLE is XMLTABLE, which uses XPATH syntax.

SELECT x.order_id, x.item_no
FROM api_logs a,
XMLTABLE(
  '/Order/Items/Item' 
  PASSING XMLTYPE(a.xml_data)
  COLUMNS 
    order_id NUMBER PATH '../../OrderID',
    item_no VARCHAR2(20) PATH 'ItemNo'
) x;

Indexing Unstructured Data#

If you are querying a massive table based on a value buried deep inside a JSON payload, a Full Table Scan will be agonizingly slow. You can create a JSON Search Index to instantly locate documents.

ALTER TABLE api_logs ADD CONSTRAINT chk_json CHECK (payload IS JSON);

-- 2. Create the index
CREATE SEARCH INDEX idx_json_payload ON api_logs(payload) FOR JSON;

Now, a query like SELECT * FROM api_logs WHERE JSON_EXISTS(payload, '$.customer?(@ == "Alice")') will use the index and return in milliseconds.

Common Gotchas#

Key Takeaways#