JSON and XML in Oracle

Modern applications exchange data as JSON or XML — REST APIs, configuration files, message queues, audit logs. Oracle has first-class support for both formats: store them in standard columns, query them with SQL, validate them, index them, and project them as relational rows.

This chapter focuses on JSON (the dominant modern format) with a shorter section on XML for completeness.

Part 1 — JSON in Oracle

Storing JSON

Oracle stores JSON inside standard text columns. Three column types are common:

Before Oracle 21c, JSON was always stored as text in a CLOB or VARCHAR2. From 21c onwards, a dedicated JSON data type stores parsed binary representation — faster queries and smaller storage.

-- Pre-21c style:
CREATE TABLE orders (
  order_id  NUMBER PRIMARY KEY,
  customer  VARCHAR2(100),
  payload   CLOB CHECK (payload IS JSON)
);

-- 21c+ style:
CREATE TABLE orders (
  order_id  NUMBER PRIMARY KEY,
  customer  VARCHAR2(100),
  payload   JSON
);

IS JSON Constraint

The IS JSON check constraint tells Oracle to enforce well-formed JSON on every insert and update:

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  payload  CLOB CHECK (payload IS JSON)
);

-- Valid:
INSERT INTO orders VALUES (1, '{"item": "widget", "qty": 5}');

-- Rejected: ORA-02290: check constraint violated
INSERT INTO orders VALUES (2, 'not-json-at-all');
INSERT INTO orders VALUES (3, '{"missing": "quote}');

Without this constraint, Oracle won't stop you inserting garbage. With it, every row is guaranteed parseable JSON — which the optimiser also uses to enable JSON indexing and query optimisations.

IS JSON accepts options:

-- Strict mode: also enforces RFC 8259 strictness (no comments, no trailing commas)
CHECK (payload IS JSON STRICT)

-- Lax mode (default): accepts JavaScript-style with single quotes and unquoted keys
CHECK (payload IS JSON LAX)

Reading JSON — Dot Notation

The simplest way to read a JSON field is dot notation in a SELECT:

INSERT INTO orders VALUES (1,
  '{ "customer": "Acme", "items": [{"sku":"A1","qty":3},{"sku":"B2","qty":5}], "total": 125.50 }');

SELECT o.payload.customer        AS customer_name,
       o.payload.total           AS order_total
FROM   orders o
WHERE  o.order_id = 1;

Result:

Dot notation requires the table to be aliased (orders o) and the column to be either typed JSON or have an IS JSON check constraint.

JSON_VALUE — Extract a Scalar

When you need a specific data type or want to query JSON without dot notation:

SELECT JSON_VALUE(payload, '$.customer')                    AS customer,
       JSON_VALUE(payload, '$.total' RETURNING NUMBER)      AS total,
       JSON_VALUE(payload, '$.items[0].sku')                AS first_sku
FROM   orders;

Result:

$ is the root of the JSON document; dots navigate objects; [n] indexes arrays (zero-based).

-- WITH options for error handling and defaults:
SELECT JSON_VALUE(payload, '$.total' 
                  RETURNING NUMBER
                  DEFAULT 0 ON ERROR
                  DEFAULT 0 ON EMPTY) AS total
FROM   orders;

By default, missing values return NULL. Use DEFAULT ... ON ERROR and DEFAULT ... ON EMPTY to provide fallbacks.

JSON_QUERY — Extract an Object or Array

JSON_VALUE only returns scalars. To return an object or array, use JSON_QUERY:

SELECT JSON_QUERY(payload, '$.items')              AS items_array,
       JSON_QUERY(payload, '$.items[0]')           AS first_item_obj,
       JSON_QUERY(payload, '$.items[*].sku' WITH WRAPPER) AS all_skus
FROM   orders;

Result:

WITH WRAPPER wraps results in [...] when there are multiple matches. $.items[*] means "every element of the items array".

JSON_EXISTS — Test If a Path Exists

-- Orders that have at least one item with sku 'A1'
SELECT order_id
FROM   orders
WHERE  JSON_EXISTS(payload, '$.items[*]?(@.sku == "A1")');

The ?( ... ) is a filter expression — @ refers to the current item in the iteration. This is the SQL/JSON equivalent of asking "does any element match this predicate?"

-- Total >= 100
WHERE JSON_EXISTS(payload, '$.total?(@ >= 100)')

-- Has both 'priority' field AND priority = 'high'
WHERE JSON_EXISTS(payload, '$.priority?(@ == "high")')

JSON_TABLE — JSON → Relational Rows

JSON_TABLE is the most powerful JSON tool: it turns a JSON document or array into a virtual table of rows you can join, filter, and aggregate like any other table.

SELECT o.order_id, t.sku, t.qty
FROM   orders o,
       JSON_TABLE(o.payload, '$.items[*]'
         COLUMNS (
           sku VARCHAR2(20) PATH '$.sku',
           qty NUMBER       PATH '$.qty'
         )
       ) t;

Result:

This unpacks the nested items array into one row per item, with each item's fields projected into columns. The order is now joinable with other tables, summable, filterable — all the things you couldn't do on the raw JSON.

-- Total quantity per order, computed from inside the JSON:
SELECT o.order_id, SUM(t.qty) AS total_qty
FROM   orders o,
       JSON_TABLE(o.payload, '$.items[*]'
         COLUMNS (qty NUMBER PATH '$.qty')) t
GROUP BY o.order_id;

JSON_TABLE accepts nested paths and even nested JSON_TABLE for deeply structured data.

Generating JSON — JSON_OBJECT and JSON_ARRAY

Construct JSON on the fly inside SELECTs:

SELECT JSON_OBJECT(
         'employeeId'   VALUE employee_id,
         'fullName'     VALUE first_name || ' ' || last_name,
         'email'        VALUE email,
         'salary'       VALUE salary
       ) AS json_emp
FROM   employees
WHERE  employee_id = 100;

Result:

{"employeeId":100,"fullName":"Steven King","email":"SKING","salary":24000}

Shorthand KEY 'name' VALUE col and 'name' VALUE col are interchangeable. Oracle 18c+ also supports JavaScript-style key:value: JSON_OBJECT(employee_id, salary).

For arrays of values:

SELECT JSON_ARRAY(first_name, last_name, salary) FROM employees;
-- ["Steven","King",24000]

Aggregate into JSON arrays of objects with JSON_ARRAYAGG:

SELECT department_id,
       JSON_ARRAYAGG(
         JSON_OBJECT('id' VALUE employee_id, 'name' VALUE first_name)
       ) AS employees_json
FROM   employees
GROUP BY department_id;

Result:

This is invaluable for building API responses directly from SQL.

Indexing JSON

Three options for speeding up JSON queries:

1. Function-based index on a specific JSON path — fastest for queries on one known field:

CREATE INDEX orders_customer_idx ON orders (JSON_VALUE(payload, '$.customer'));

-- Now this query uses the index:
WHERE JSON_VALUE(payload, '$.customer') = 'Acme';

2. JSON Search Index — Oracle Text-style, indexes the entire document:

CREATE SEARCH INDEX orders_search_idx ON orders (payload) FOR JSON;

Speeds up JSON_EXISTS and full-document searches but consumes more disk space.

3. Materialised view — extract frequently-queried JSON fields into actual columns:

CREATE MATERIALIZED VIEW orders_mv
REFRESH FAST ON COMMIT
AS SELECT order_id,
          JSON_VALUE(payload, '$.customer') AS customer,
          JSON_VALUE(payload, '$.total' RETURNING NUMBER) AS total
   FROM   orders;

Trade storage for query speed when the same JSON paths are read repeatedly.

JSON Performance Notes

• The JSON type (21c+) is 2-5× faster than CLOB+IS JSON for most operations because parsing happens once at insert time.
• For frequently accessed scalar fields, function-based indexes are essential — without them, every read re-parses the JSON.
• Avoid JSON_VALUE in JOIN conditions — Oracle can't always use indexes through them. Pre-extract into a column or view first.
• JSON_TABLE over a large CLOB can be slow; consider denormalising hot fields into real columns.

Part 2 — XML in Oracle

Oracle's XML support is mature (decades old, pre-dating JSON). It's still common in industries where XML is the standard exchange format — finance (FIX, SWIFT), healthcare (HL7), government (XBRL).

Storing XML — XMLType

The dedicated XMLType column type stores XML data in a parsed, queryable form:

CREATE TABLE orders_xml (
  order_id NUMBER PRIMARY KEY,
  doc      XMLType
);

INSERT INTO orders_xml VALUES (1,
  XMLType('<order>
             <customer>Acme</customer>
             <items>
               <item sku="A1" qty="3"/>
               <item sku="B2" qty="5"/>
             </items>
             <total>125.50</total>
           </order>'));

Querying XML — XMLQUERY

XMLQUERY uses XPath/XQuery to extract values:

SELECT XMLQUERY('/order/customer/text()'
                PASSING doc
                RETURNING CONTENT) AS customer
FROM   orders_xml;

Result: Acme

XPath syntax:

• /order/customer — child elements
• //item — any descendant item element
• /order/items/item[@sku="A1"] — predicates on attributes
• text() — extract text content

Extracting Scalar Values — EXTRACTVALUE

For simple single-value extraction, EXTRACTVALUE is slightly less verbose than XMLQUERY:

SELECT EXTRACTVALUE(doc, '/order/total') AS total,
       EXTRACTVALUE(doc, '/order/customer') AS customer
FROM   orders_xml;

XML → Relational Rows — XMLTABLE

The XML equivalent of JSON_TABLE:

SELECT o.order_id, t.sku, t.qty
FROM   orders_xml o,
       XMLTABLE('/order/items/item'
         PASSING o.doc
         COLUMNS sku VARCHAR2(20) PATH '@sku',
                 qty NUMBER       PATH '@qty'
       ) t;

Result:

@sku reads the XML attribute sku. Element values use the element name as the path.

Generating XML — XMLELEMENT and XMLAGG

SELECT XMLELEMENT("employee",
         XMLELEMENT("id",   employee_id),
         XMLELEMENT("name", first_name || ' ' || last_name),
         XMLELEMENT("email", email)
       ) AS emp_xml
FROM   employees
WHERE  employee_id = 100;

Result: <employee><id>100</id><name>Steven King</name><email>SKING</email></employee>

Combine with XMLAGG for groups:

SELECT department_id,
       XMLELEMENT("department",
         XMLATTRIBUTES(department_id AS "id"),
         XMLAGG(XMLELEMENT("employee", first_name))
       ) AS dept_xml
FROM   employees
GROUP BY department_id;

JSON vs XML — Which to Use?

Default to JSON for new systems unless an existing integration mandates XML. The Oracle tooling is comparable; the developer ecosystem is heavily JSON-favoured.

Common Errors

Interview Corner

CREATE TABLE orders (
  order_id   NUMBER PRIMARY KEY,
  customer   VARCHAR2(100),       -- promoted: searched often
  status     VARCHAR2(20),        -- promoted: indexed
  total      NUMBER,              -- promoted: aggregated
  payload    JSON                  -- rest of the document
);

JSON_VALUE(payload, '$.customer')    -- 'Acme'        (scalar)
JSON_VALUE(payload, '$.total')       -- 125.50        (scalar)
JSON_QUERY(payload, '$.items')       -- '[{"sku":"A1","qty":3}]'  (array)
JSON_QUERY(payload, '$.items[0]')    -- '{"sku":"A1","qty":3}'    (object)

JSON_TABLE(payload, '$.items[*]' COLUMNS (sku VARCHAR2(20), qty NUMBER))
-- Returns table:  sku='A1', qty=3

CREATE INDEX o_status_idx ON orders (JSON_VALUE(payload, '$.status'));

-- Now this query uses the index:
WHERE JSON_VALUE(payload, '$.status') = 'OPEN';

CREATE SEARCH INDEX o_search_idx ON orders (payload) FOR JSON;

ALTER TABLE orders ADD (status VARCHAR2(20) GENERATED ALWAYS AS 
                        (JSON_VALUE(payload, '$.status')) VIRTUAL);
CREATE INDEX o_status_idx ON orders (status);

Related Topics

• DDL Commands — defining tables with JSON and XMLType columns
• DML Commands — inserting and updating JSON documents
• Views — materialised views to denormalise hot JSON fields
• Indexes — function-based and JSON Search indexes
• Performance — when to extract JSON fields vs storing as relational columns