JSON, XML and Large Objects

Applications read and write JSON documents while the data stays normalized in relational tables.

31.1 JSON in Oracle

JSON Schema validation (23ai): doc JSON VALIDATE '{...schema...}'.

SODA (Simple Oracle Document Access) and the Oracle Database API for MongoDB.

CREATE TABLE events (id NUMBER PRIMARY KEY, doc JSON);                     -- 21c+ native type
-- Pre-21c: doc VARCHAR2/CLOB/BLOB CHECK (doc IS JSON)

INSERT INTO events VALUES (1, '{"type":"click","user":{"id":7,"city":"Pune"},"tags":["vip","new"]}');

SELECT e.doc.type, e.doc.user.city FROM events e;                          -- dot notation
SELECT JSON_VALUE(doc, '$.user.id' RETURNING NUMBER),
       JSON_QUERY(doc, '$.tags'),
       JSON_EXISTS(doc, '$.user?(@.city == "Pune")')
FROM events;

SELECT jt.* FROM events,
     JSON_TABLE(doc, '$' COLUMNS (
         type VARCHAR2(20) PATH '$.type',
         user_id NUMBER PATH '$.user.id',
         NESTED PATH '$.tags[*]' COLUMNS (tag VARCHAR2(20) PATH '$'))) jt;

-- Generate JSON
SELECT JSON_OBJECT('id' VALUE employee_id, 'name' VALUE last_name) FROM employees;
SELECT JSON_ARRAYAGG(JSON_OBJECT(*) RETURNING CLOB) FROM departments;
SELECT JSON_SERIALIZE(doc PRETTY) FROM events;

-- Modify
UPDATE events SET doc = JSON_TRANSFORM(doc, SET '$.status' = 'processed', REMOVE '$.tags');

-- Index
CREATE SEARCH INDEX ix_events_search ON events (doc) FOR JSON;
CREATE INDEX ix_events_type ON events e (e.doc.type.string());
CREATE MULTIVALUE INDEX 
…

31.2 JSON Relational Duality Views (23ai)

Applications read and write JSON documents while the data stays normalized in relational tables. Lock-free optimistic concurrency uses ETAGs.

CREATE JSON RELATIONAL DUALITY VIEW orders_dv AS
SELECT JSON {'_id' : o.order_id, 'status' : o.status,
             'customer' : (SELECT JSON {'id' : c.customer_id, 'name' : c.first_name} FROM customers c WITH NOUPDATE
                           WHERE c.customer_id = o.customer_id),
             'items' : [SELECT JSON {'product' : i.product_id, 'qty' : i.quantity}
                        FROM order_items i WITH INSERT UPDATE DELETE WHERE i.order_id = o.order_id]}
FROM orders o WITH INSERT UPDATE DELETE;

31.3 XML DB

CREATE TABLE po_xml (id NUMBER, doc XMLTYPE);
SELECT XMLQUERY('/order/item/@sku' PASSING doc RETURNING CONTENT) FROM po_xml;
SELECT x.* FROM po_xml, XMLTABLE('/order/item' PASSING doc COLUMNS sku VARCHAR2(10) PATH '@sku', qty NUMBER PATH '@qty') x;
SELECT XMLELEMENT("emp", XMLATTRIBUTES(employee_id AS "id"), last_name) FROM employees;
SELECT XMLAGG(XMLELEMENT("name", last_name)) FROM employees;
-- XMLEXISTS, XMLSERIALIZE, XMLFOREST, XML schema registration, XMLIndex

31.4 Large Objects (LOBs)

CLOB, NCLOB, BLOB, BFILE. SecureFiles features: compression, deduplication, encryption.

LOB locators, temporary LOBs, inline vs out-of-line storage (ENABLE STORAGE IN ROW), CACHE vs NOCACHE.

DBMS_LOB operations and loading from files with DBMS_LOB.LOADBLOBFROMFILE.