Published: 2026-05-12

Oracle JSON Guide: 12c to 26ai, MongoDB Compatibility

Oracle has been investing in JSON longer than most teams realise. JSON_VALUE shipped in 12c (2014). JSON_TABLE followed shortly after. The native JSON type arrived in 21c, and Oracle now exposes a MongoDB-compatible API on top. Here's the practical guide. See the Oracle JSON Developer's Guide for the authoritative reference.

Quick Answers

Q: Does Oracle Database have a JSON data type?

A: Yes — Oracle 21c added a native JSON data type that stores documents in OSON, an optimised binary format. 23ai and the current Oracle AI Database 26ai (release 23.26.1, GA January 2026) carry it forward. Earlier versions (12c, 18c, 19c) stored JSON in VARCHAR2, CLOB, or BLOB columns with an IS JSON CHECK constraint.

Q: What is the difference between JSON_VALUE, JSON_QUERY, and JSON_TABLE?

A: JSON_VALUE returns a scalar at a path. JSON_QUERY returns a JSON fragment. JSON_TABLE projects a JSON array into a SQL rowset so you can join, group, and aggregate over array elements as if they were a real table.

Q: How do I index a JSON path in Oracle?

A: Three patterns — function-based indexes on JSON_VALUE for known scalar paths, JSON Search indexes (CREATE SEARCH INDEX ... FOR JSON) for ad-hoc queries across the document, and multi-value function-based indexes on JSON arrays (21c+). The Oracle Text option adds full-text JSON search.

Q: Can Oracle replace MongoDB?

A: Oracle ships SODA (Simple Oracle Document Access) plus the Oracle Database API for MongoDB (23ai/26ai), which lets MongoDB drivers connect directly to Oracle without code changes. For document workloads inside an Oracle estate, replacing MongoDB is realistic; for greenfield document-only systems, MongoDB has lighter operational overhead.

A Decade of Oracle JSON

• Oracle 12c (12.1.0.2) — JSON support added. IS JSON CHECK constraint, JSON_VALUE, JSON_QUERY, JSON_EXISTS. JSON stored in VARCHAR2, CLOB, or BLOB.
• Oracle 12.2 — JSON_TABLE, JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG constructors; JSON Data Guide for automatic schema discovery; JSON dot-notation access syntax; SODA-for-REST (via ORDS).
• Oracle 18c — SODA-for-PL/SQL and SODA-for-Java; TREAT(... AS JSON); LOB-returning enhancements for the JSON constructors.
• Oracle 19c — JSON_MERGEPATCH, JSON_SERIALIZE, mapping of JSON to/from SQL object types; expanded standards conformance.
• Oracle 21c — native JSON data type with OSON binary storage, in-place piecewise updates, and faster path access than text-based JSON (Oracle benchmarks claim several-fold gains depending on workload). Multi-value function-based JSON indexes.
• Oracle 23ai — JSON Relational Duality, JSON Schema validation, JSON_TRANSFORM, MongoDB-wire-compatible API (Oracle Database API for MongoDB) on the database itself.
• Oracle AI Database 26ai (2026, release 23.26.1) — long-term-support release that replaces Oracle Database 23ai (current GA on Linux x86-64 as of May 2026). Carries forward the full 23ai JSON stack (Duality, JSON Schema, JSON_TRANSFORM, Database API for MongoDB); GA for Linux x86-64 on-prem in January 2026.

If you maintain anything older than 21c, you're in the VARCHAR2 + IS JSON world. If you're on 21c or later, prefer the native type for everything new.

Storage Choices Today

Oracle exposes more JSON storage choices than any other engine:

1. Native JSON type (21c+) — OSON binary storage, fast access, validated on insert.
2. VARCHAR2(N) with IS JSON — works in 12c+. Cap on column size (32767 bytes when MAX_STRING_SIZE = EXTENDED).
3. CLOB with IS JSON — for documents over the VARCHAR2 cap. Slower access pre-21c because parsing happens per query.
4. BLOB with IS JSON — binary-safe, sometimes used for documents stored verbatim from external systems.

Querying JSON: SQL/JSON Functions and Dot Notation

Oracle implements the SQL/JSON standard plus a distinctive dot-notation shorthand. The JSON_TABLE reference covers the full path-expression and column-projection grammar:

-- Scalar extraction (the workhorse)
SELECT * FROM customers
WHERE JSON_VALUE(data, '$.email') = '[email protected]';

-- Sub-document extraction
SELECT JSON_QUERY(data, '$.address') FROM customers;

-- Path existence
SELECT * FROM customers WHERE JSON_EXISTS(data, '$.phone');

-- Dot notation (19c+, requires IS JSON or native JSON)
SELECT c.data.email.string() FROM customers c
WHERE c.data.address.city.string() = 'Berlin';

-- Shred a JSON array into rows
SELECT o.id, j.sku, j.qty
FROM orders o,
     JSON_TABLE(o.items, '$[*]'
       COLUMNS (sku VARCHAR2(64) PATH '$.sku',
                qty NUMBER       PATH '$.qty')) j;

The dot-notation form is one of Oracle's most distinctive JSON ergonomics — it lets you traverse JSON like a regular object. It only works on columns declared IS JSON or of the native JSON type.

Indexing JSON Paths

1. Function-based index on a known path

CREATE INDEX idx_customers_email
  ON customers (JSON_VALUE(data, '$.email'
    RETURNING VARCHAR2(255)));

2. JSON Search index for ad-hoc queries

The Oracle equivalent of PostgreSQL's GIN-on-jsonb. Indexes every key and value in the document so any JSON_EXISTS / JSON_VALUE filter benefits:

CREATE SEARCH INDEX idx_customers_search
  ON customers (data) FOR JSON;

3. Materialised view for hot read paths

For dashboards or APIs that always pull the same shape, materialise a relational projection over JSON_TABLE and refresh on commit. You get an indexed relational table whose source of truth is the JSON column.

SODA: Document API on Top of Oracle

SODA (Simple Oracle Document Access) lets your application talk to Oracle as if it were a document store: insert(doc), find().filter(qbe).getDocuments(). Internally, the documents land in Oracle tables you can still query with SQL. SODA-for-REST shipped with ORDS in 12.2 (2016); SODA-for-PL/SQL and SODA-for-Java arrived in 18c. This bridge is the most underappreciated Oracle feature for teams considering MongoDB.

Oracle 23ai goes further with a wire-compatible MongoDB API: existing MongoDB drivers can connect to Oracle Database without code changes, treating it as a MongoDB cluster. As of May 2026, this ships in both Oracle Database 23ai and Oracle AI Database 26ai (23.26.1). See the Oracle Database API for MongoDB overview for the protocol and driver compatibility list.

Oracle JSON vs MongoDB

For teams already on Oracle, the case to add MongoDB has weakened year over year. The detailed comparison:

Pick Oracle JSON when you already run Oracle. The marginal cost of adding document storage to an existing Oracle estate is small; the cost of adopting and operating MongoDB alongside it is high.

Pick MongoDB when you're greenfield and document-only, your team prefers BSON-native tooling, or you want a managed cloud service like Atlas without the Oracle licensing footprint.

A Realistic Schema Pattern

CREATE TABLE events (
  id          NUMBER GENERATED AS IDENTITY PRIMARY KEY,
  user_id     NUMBER       NOT NULL,
  event_type  VARCHAR2(64) NOT NULL,
  occurred_at TIMESTAMP    DEFAULT SYSTIMESTAMP NOT NULL,
  data        JSON         NOT NULL  -- 21c / 23ai / 26ai; CLOB CHECK (data IS JSON) earlier
);

CREATE INDEX idx_events_user_time
  ON events (user_id, occurred_at DESC);

CREATE INDEX idx_events_name
  ON events (JSON_VALUE(data, '$.name'
    RETURNING VARCHAR2(128)));

Working with Oracle JSON in a SQL IDE

Oracle JSON has historically been brutal in SQL Developer and most generic tools: CLOB-stored JSON appears as (CLOB) placeholder text and you have to double-click to a separate tab to see it. The native JSON type is better, but most IDEs still render it as one-line escaped text.

Jam SQL Studio recognises Oracle's native JSON type and lets you declare CLOB / VARCHAR2 columns as JSON in MetaInfo — the operator dropdown then exposes JSON path filtering, with the engine-correct data IS JSON guard wrapped around the predicate automatically. Cells render as collapsible trees, the filter chip exposes path-aware sub-operators that compile to JSON_VALUE, JSON_EXISTS, and dot-notation, and a Peek popover scans the loaded rows to show every JSON path with its prevalence. The same UI works on every supported Oracle release from 12c through 26ai — same mental model regardless of whether the column is native JSON or CLOB CHECK (... IS JSON).

Last verified against the Oracle JSON Developer's Guide and Oracle AI Database 26ai release notes on 2026-05-13.