JSON/XML Types in Oracle

Handling JSON and XML Data in Oracle

Oracle provides built-in support for JSON and XML data storage, querying, and manipulation. Below are methods to handle both data types efficiently.

1. Handling JSON Data in Oracle

A. Storing JSON Data

JSON can be stored in:
- CLOB (Character Large Object)
- VARCHAR2 (for small JSON documents)
- BLOB
- Native JSON Data Type (from Oracle 21c)

Example: Creating a Table with JSON Column

CREATE TABLE employees (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    details CLOB CHECK (details IS JSON) -- Enforce JSON format
);

B. Inserting JSON Data

INSERT INTO employees VALUES (1, 'John Doe', '{"age": 30, "role": "Developer"}');

C. Querying JSON Data

1. Extracting JSON Values Using `JSON_VALUE`

SELECT name, JSON_VALUE(details, '$.age') AS age
FROM employees
WHERE JSON_VALUE(details, '$.role') = 'Developer';

$.age → Extracts age field.
$.role → Filters JSON objects where role = Developer.

2. Querying JSON Data Using `JSON_TABLE`

SELECT emp_id, jt.age, jt.role
FROM employees,
     JSON_TABLE(details, '$' COLUMNS (age NUMBER PATH '$.age', role VARCHAR2(100) PATH '$.role')) jt;

Converts JSON fields into relational table format.

D. Updating JSON Data

UPDATE employees 
SET details = JSON_TRANSFORM(details, SET '$.age' = 35)
WHERE id = 1;

JSON_TRANSFORM modifies JSON fields without rewriting the entire JSON document.

E. Indexing JSON Data

For faster retrieval, create a JSON Search Index:

CREATE INDEX emp_json_idx ON employees (details) INDEXTYPE IS JSON;

2. Handling XML Data in Oracle

A. Storing XML Data

XML can be stored in:

CLOB
XMLType (Oracle’s optimized XML storage type)

Example: Creating a Table with XML Column

CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    order_details XMLTYPE
);

B. Inserting XML Data

INSERT INTO orders VALUES (101, XMLTYPE('<order><customer>John</customer><total>100</total></order>'));

C. Querying XML Data

1. Extracting XML Data Using `EXTRACTVALUE`

SELECT order_id, EXTRACTVALUE(order_details, '/order/customer') AS customer
FROM orders;

Retrieves customer name from the XML structure.

2. Using `XMLTABLE` for More Complex Queries

SELECT o.order_id, x.customer, x.total
FROM orders o,
     XMLTABLE('/order' PASSING o.order_details
              COLUMNS customer VARCHAR2(100) PATH 'customer',
                      total NUMBER PATH 'total') x;

Converts XML fields into relational table format.

D. Updating XML Data

Using XMLQuery and XMLModify:

UPDATE orders
SET order_details = XMLModify(order_details, 'replace value of (/order/total/text())[1] with "150"')
WHERE order_id = 101;

Updates the total amount in the XML.

E. Indexing XML Data

For fast lookups, create an XML Index:

CREATE INDEX order_xml_idx ON orders (order_details) INDEXTYPE IS XDB.XMLINDEX;

3. JSON vs. XML in Oracle

Feature	JSON	XML
Storage	CLOB, VARCHAR2, BLOB, JSON Type	XMLType, CLOB
Query Language	SQL with JSON functions	XPath, XQuery
Performance	Faster for small, simple documents	Better for hierarchical, large data
Indexing	JSON Index (`JSON_VALUE`, `JSON_TABLE`)	XML Index (`XMLIndex`)

Conclusion

Use JSON for lightweight, structured data (API responses, logging).
Use XML when hierarchical structure and schema validation are needed.
Indexes improve performance for querying both JSON and XML.
Conversion Functions (JSON_TABLE, XMLTABLE) help integrate them with relational queries.