Native JSON Storage, SQL/JSON Queries, JSON Relational Duality Concepts, and Indexing

Learning outcomes

ServiceHub receives vendor-specific work-order payloads whose optional fields change frequently. Putting every field into nullable relational columns creates churn, but moving the whole workload to a separate document database would duplicate identity, transactions, backup, authorization and operational tooling. Oracle's native JSON data type stores parsed JSON in Oracle's optimized binary representation (OSON), while SQL/JSON functions let relational SQL query and generate document shapes. JSON Relational Duality Views solve a different problem: they materialize a JSON document view over normalized relational tables without storing a second copy.

1. Native JSON is typed database data, not just a CLOB with braces

SQL data type JSON stores JSON using Oracle's native binary OSON format. Oracle validates/parses values as JSON when they enter the column and preserves JSON-language typing. Native JSON avoids repeated text parsing and enables newer indexing/document features. Textual JSON in VARCHAR2/CLOB/BLOB remains useful for compatibility, but it is a different storage model.

SELECT name,valueFROM v$parameterWHERE name='compatible';-- Native JSON type requires COMPATIBLE >= 20.

2. Build a small native JSON collection inside the ServiceHub schema

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh24_event_docs PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh24_event_docs (  event_id NUMBER PRIMARY KEY,  event_ts TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  document JSON NOT NULL);INSERT INTO sh24_event_docs(event_id,document)VALUES(  1,  JSON('{    "workOrderId": 1001,    "status": "OPEN",    "priority": 2,    "tags": ["hvac","urgent"],    "device": {"model":"DX-10","temperature":28.4}  }'));INSERT INTO sh24_event_docs(event_id,document)VALUES(  2,  JSON('{    "workOrderId": 1002,    "status": "CLOSED",    "priority": 4,    "tags": ["electrical"],    "device": {"model":"EL-5","temperature":22.1}  }'));COMMIT;

If the text is malformed, conversion to native JSON fails at insertion instead of silently preserving invalid JSON text. That validation is part of the storage contract.

3. SQL/JSON operators answer different questions

SELECT  event_id,  json_value(document,'$.status'    RETURNING VARCHAR2(12)    ERROR ON ERROR) AS status_code,  json_value(document,'$.priority'    RETURNING NUMBER    ERROR ON ERROR) AS priorityFROM sh24_event_docsORDER BY event_id;

SELECT  event_id,  json_serialize(    json_query(document,'$.device')    PRETTY  ) AS device_jsonFROM sh24_event_docsORDER BY event_id;

SELECT event_idFROM sh24_event_docs dWHERE json_exists(  d.document,  '$.tags[*]?(@ == "urgent")');-- Expected: event_id 1.

SELECT  d.event_id,  jt.tagFROM sh24_event_docs d,     JSON_TABLE(       d.document,       '$.tags[*]'       COLUMNS (         tag VARCHAR2(30) PATH '$'       )     ) jtORDER BY d.event_id,jt.tag;

JSON_VALUE targets one scalar; JSON_QUERY returns JSON object/array content; JSON_EXISTS answers a Boolean path/filter question; JSON_TABLE relationalizes JSON into rows/columns so normal joins, constraints and SQL processing can continue.

4. Generate JSON from relational data without concatenating strings

SELECT JSON_OBJECT(         'eventId' VALUE event_id,         'status'  VALUE json_value(document,'$.status'),         'source'  VALUE 'ServiceHub'         RETURNING JSON       ) AS summary_docFROM sh24_event_docsORDER BY event_id;

JSON generation functions handle quoting/escaping/type semantics. Manual string concatenation is fragile when values contain quotes, Unicode, nulls or nested structures.

5. Index the path you actually query

CREATE INDEX sh24_json_status_ixON sh24_event_docs(  JSON_VALUE(    document,    '$.status'    RETURNING VARCHAR2(12)    ERROR ON ERROR  ));SELECT /*+ gather_plan_statistics */       COUNT(*)FROM sh24_event_docsWHERE JSON_VALUE(        document,        '$.status'        RETURNING VARCHAR2(12)        ERROR ON ERROR      )='OPEN';SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

The query's return type/path/error semantics should match the indexed expression. A tiny two-row table may still full-scan because that is cheaper; the index definition does not force index use.

6. 26ai simplified JSON indexes and JSON search indexes serve different workloads

CREATE JSON SINGLEVALUE INDEX sh24_json_priority_ixON sh24_event_docs d (  d.document.priority.number());

CREATE JSON MULTIVALUE INDEX sh24_json_tags_mviON sh24_event_docs d (  d.document.tags[*].string());

A single-value index is appropriate for scalar fields; a multivalue index targets arrays/repeated values. For ad-hoc structural/full-text JSON queries, Oracle provides an Oracle Text-backed JSON search index:

CREATE SEARCH INDEX sh24_json_search_ixON sh24_event_docs(document)FOR JSON;

Known stable path → prefer a targeted B-tree/function/JSON path index. Array membership → multivalue. Ad-hoc/full-text exploration → JSON search index, accepting its domain-index maintenance/storage costs. Do not create all of them “just in case.”

7. Duality: document API over normalized relational storage

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh24_wo_note PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh24_wo PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh24_wo (  work_order_id NUMBER PRIMARY KEY,  status_code   VARCHAR2(12) NOT NULL    CHECK (status_code IN ('OPEN','HOLD','CLOSED')),  priority_code NUMBER NOT NULL    CHECK (priority_code BETWEEN 1 AND 5));CREATE TABLE sh24_wo_note (  note_id       NUMBER PRIMARY KEY,  work_order_id NUMBER NOT NULL    REFERENCES sh24_wo(work_order_id),  note_text     VARCHAR2(200) NOT NULL);INSERT INTO sh24_wo VALUES(9001,'OPEN',2);INSERT INTO sh24_wo_note VALUES(1,9001,'Inspect compressor');INSERT INTO sh24_wo_note VALUES(2,9001,'Bring replacement filter');COMMIT;

CREATE JSON RELATIONAL DUALITY VIEW sh24_work_order_dv AS  sh24_wo @insert @update @delete {    _id      : work_order_id,    status   : status_code,    priority : priority_code,    notes    : sh24_wo_note @insert @update @delete [      {        noteId   : note_id,        noteText : note_text      }    ]  };

The document is materialized from table rows; it is not a second stored copy. The PK/FK/check constraints remain relational truth, while the application can consume one hierarchical JSON object.

8. Observe document metadata and dictionary state

SELECT JSON_SERIALIZE(data PRETTY)FROM sh24_work_order_dvWHERE JSON_VALUE(data,'$._id' RETURNING NUMBER)=9001;SELECT  view_name,  root_table_name,  root_table_owner,  read_onlyFROM user_json_duality_viewsWHERE view_name='SH24_WORK_ORDER_DV';

Duality documents automatically contain _metadata.etag. By default the ETAG hashes CHECKable document content and supports optimistic concurrency: the application can read → edit → write only if database state still matches what it read.

9. Deliberately stale update: ORA-42699 prevents a lost write

Session B first stores the full JSON document returned by the view—including its _metadata.etag—as :stale_document. Session A then commits a relational update:

UPDATE sh24_woSET priority_code=1WHERE work_order_id=9001;COMMIT;

Session B modifies its older document locally and sends that stale version back:

UPDATE sh24_work_order_dvSET data=:stale_documentWHERE JSON_VALUE(data,'$._id' RETURNING NUMBER)=9001;-- Expected:-- ORA-42699: Cannot update JSON Relational Duality View ...-- The ETAG ... in the database did not match the ETAG passed in.

The safe repair is to fetch the newest document, reconcile the other writer's change, modify that current copy, and retry. Annotating fields @nocheck removes them from ETAG conflict detection and should be an explicit semantic decision—not an error workaround.

10. Constraints still win

UPDATE sh24_woSET priority_code=99WHERE work_order_id=9001;-- Expected check-constraint failure (ORA-02290).ROLLBACK;

An updatable document view does not turn normalized constrained data into schemaless storage. Primary/unique/not-null/referential/check constraints can reject document DML when the document would violate the relational model.

11. Security/client/replication boundaries

Duality views have current 26ai restrictions. A pre-21/19c SQL*Plus client cannot handle the native JSON DATA column correctly; use current SQLcl/SQL Developer/JDBC/etc. VPD on the duality view itself and FGA on duality views are not supported; VPD on underlying tables has specific all-statement requirements. GoldenGate logical replication has explicit duality-view prerequisites/restrictions and is not part of this Free lab.

12. Cleanup

DROP JSON RELATIONAL DUALITY VIEW sh24_work_order_dv;DROP TABLE sh24_wo_note PURGE;DROP TABLE sh24_wo PURGE;DROP INDEX sh24_json_search_ix;DROP INDEX sh24_json_tags_mvi;DROP INDEX sh24_json_priority_ix;DROP INDEX sh24_json_status_ix;DROP TABLE sh24_event_docs PURGE;

13. Production judgment

Use native JSON for genuinely variable document fields whose JSON semantics matter. Keep stable identities, relationships and invariants relational when that model is clearer. Use a duality view when applications want document-shaped CRUD over normalized shared data—not merely because JSON is fashionable. Index from observed path/query workload, not document size.

Native JSON requires COMPATIBLE >= 20. Duality views are a 26ai feature; no separate management pack is involved. Creation is schema/PDB scoped and requires normal table/view/object privileges. No restart is needed. Current RU 23.26.3 also adds further duality-view capabilities such as user-defined identifying keys/array-limit enhancements, but the lab stays on stable core syntax. Lesson 2 turns to the other structured-document estate that production databases frequently inherit: XML.

Authoritative references

• JSON in Oracle AI Database — native JSON/SQL integration
• Overview of Indexing JSON Data — scalar/multivalue/search indexing
• CREATE JSON RELATIONAL DUALITY VIEW — 26ai duality syntax
• Optimistic Concurrency with Duality Views — ETAG/lost-update behavior
• 26ai Duality View Restrictions — client/security/replication restrictions