Oracle JSON_TABLE for Turning Documents Into Rows Explained

Oracle has had SQL/JSON in the database since 12c, and JSON_TABLE is the centerpiece: it projects a JSON document into rows and columns so that the rest of SQL can work on it as if it were an ordinary table. It is the function that other databases borrowed the name from (MySQL and PostgreSQL both have a JSON_TABLE today), and Oracle's version has the longest list of options. This article walks through the structure of a call, nested arrays, the error and empty clauses, the type defaults that trip people, and the alternatives for single values.

Storing the document

JSON in Oracle lives in a VARCHAR2, CLOB or BLOB column with an IS JSON check constraint, or, from 21c, in the native JSON data type:

CREATE TABLE orders (
  id  NUMBER PRIMARY KEY,
  doc CLOB CHECK (doc IS JSON)
);

IS JSON rejects text that is not valid JSON on insert. The STRICT keyword (IS JSON (STRICT)) enforces the standard grammar; the default accepts a relaxed form that allows unquoted field names and single quotes. Decide which you want. Relaxed mode means {a:1} is stored and every other tool will reject it. The constraint also enables Oracle's dot notation (o.doc.customer.name) and helps the optimizer use JSON features.

The call

SELECT o.id, jt.line, jt.sku, jt.qty, jt.price
FROM orders o,
     JSON_TABLE(o.doc, '$.items[*]'
       COLUMNS (
         line  FOR ORDINALITY,
         sku   VARCHAR2(20) PATH '$.sku',
         qty   NUMBER       PATH '$.qty',
         price NUMBER(10,2) PATH '$.price'
       )) jt;

The first argument is the document. The second, '$.items[*]', is the row path: every match becomes one output row, and for each match the COLUMNS paths are evaluated relative to it. The comma between the table and JSON_TABLE makes it a correlated join, as in the other databases, so the function sees each order's own document. Two orders with three and two items would give five rows.

Column types are Oracle types. A path that resolves to a number is converted to NUMBER, a string to VARCHAR2, and a mismatch goes through the error clause below. Column kinds:

• **name type PATH path**: a scalar value.

• **FOR ORDINALITY**: a running row number within each document, starting at 1.

• **name type EXISTS PATH path**: true or false for whether the path matches anything (for a NUMBER or VARCHAR2 column that is 1/0 or 'true'/'false', by the declared type).

• **name type FORMAT JSON PATH path**: the value kept as JSON text, for an object or array you will process further. Add WITH WRAPPER to wrap multiple matches in an array.

• **NESTED PATH path COLUMNS (...)**: unpack an inner array.

Nested arrays

A document with order lines inside orders, and options inside lines, needs one JSON_TABLE rather than a chain:

SELECT jt.*
FROM orders o,
     JSON_TABLE(o.doc, '$'
       COLUMNS (
         cust_id NUMBER PATH '$.customer.id',
         NESTED PATH '$.items[*]'
           COLUMNS (sku VARCHAR2(20) PATH '$.sku', qty NUMBER PATH '$.qty')
       )) jt;

The outer columns (cust_id) repeat on each row from the nested array. Per Oracle's documentation, a nested path behaves like an outer join: a document whose items array is empty or missing still produces a row, with NULL in the nested columns. That differs from OPENJSON in SQL Server, where an empty array drops the row with CROSS APPLY. Two sibling nested paths in one call are combined as a union with NULL padding, not as a Cartesian product, according to the documentation, which matches what PostgreSQL and MySQL do (JSON_TABLE in MySQL and PostgreSQL 17 both produce the same shape on the same input).

For a top-level array, use '$[*]' as the row path. For a single object, use '$'.

Errors, empties and defaults

Each column accepts ON ERROR and ON EMPTY:

coupon VARCHAR2(10) PATH '$.coupon' DEFAULT 'none' ON EMPTY
qty    NUMBER       PATH '$.qty'    NULL ON ERROR
sku    VARCHAR2(20) PATH '$.sku'    ERROR ON EMPTY

• Empty means the path found nothing. The options are NULL (the default), ERROR and DEFAULT literal.

• Error means the path found something that cannot be used: a value of the wrong type or one that is too long, or an object where a scalar was expected. The options are the same, and the default is NULL.

So an unadorned query is forgiving: wrong types become NULL silently, exactly as in MySQL. For a load that must not lose data, set ERROR ON ERROR and ERROR ON EMPTY on the required columns. The error you will see for an empty result is ORA-40462: JSON_VALUE evaluated to no value, and for syntax problems in the document ORA-40441: JSON syntax error. There is also a table-level ERROR ON ERROR clause that applies to the whole function.

The 4000-byte default

A scalar column without a size defaults to VARCHAR2(4000), and a longer value is an error: ORA-40478: output value too large (maximum: 4000) appears when the data is longer than the declared size and ERROR ON ERROR is in force, or becomes NULL otherwise. The bug appears in production, not in testing, because test data is short. Declare the size you need, or use CLOB where the version allows, or add TRUNCATE to the column to cut the value to fit. In 12.2 and later VARCHAR2(32767) is available when MAX_STRING_SIZE=EXTENDED.

Related trap: Oracle treats an empty string as NULL. A JSON value "" in a VARCHAR2 column reads back as NULL, which is the same as a missing key unless you use EXISTS PATH to tell them apart.

Other functions in the family

JSON_TABLE is the heavy tool. For one value, the lighter functions are:

SELECT JSON_VALUE(doc, '$.customer.name')                       FROM orders;
SELECT JSON_VALUE(doc, '$.total' RETURNING NUMBER)              FROM orders;
SELECT JSON_QUERY(doc, '$.items' WITH WRAPPER)                  FROM orders;
SELECT id FROM orders WHERE JSON_EXISTS(doc, '$.items[*]?(@.qty > 5)');

JSON_VALUE returns a scalar (text by default, typed with RETURNING), JSON_QUERY returns an object or array as JSON text, and JSON_EXISTS tests a path with an optional filter, which is how you filter documents by the contents of an array without unpacking it. Their ON ERROR / ON EMPTY clauses work the same way as in JSON_TABLE. The filter syntax ?(@.qty > 5) is Oracle's SQL/JSON path filter, the same idea as PostgreSQL's jsonpath (extracting nested JSONB values with path operators).

With the IS JSON constraint in place, dot notation is a shorthand: SELECT o.doc.customer.name FROM orders o is a JSON_VALUE call and requires a table alias. It is convenient for ad hoc queries, and it returns a JSON-typed result unless you cast it, so use JSON_VALUE with RETURNING in application code.

Indexing and speed

JSON_TABLE output is computed at query time and cannot be indexed. What can be indexed is the source: a function-based index on JSON_VALUE(doc, '$.customer.name') makes lookups by that field fast; a JSON search index (CREATE SEARCH INDEX ... ON orders (doc) FOR JSON) supports ad hoc JSON_EXISTS and JSON_VALUE predicates across the document; and from 21c, multivalue indexes handle arrays. If a particular unpacking runs constantly, Oracle's documentation describes materialized views over JSON_TABLE, which store the projected rows. At that point the data is relational, and a real child table (loaded once) is simpler to reason about. For why that migration pays off, see when JSONB is the wrong choice, whose lessons are database-independent. The comparison across engines is in JSON in SQL Server, Oracle, Snowflake and BigQuery compared.

When JSON_TABLE is the wrong tool in Oracle

You need one scalar. JSON_VALUE is shorter, and the dot notation shorter still for exploration.

You only need to know whether something is present. JSON_EXISTS with a filter avoids unpacking every element.

The columns are long text. Remember the 4000-byte default and the empty-string rule.

You need the document edited, not read. JSON_TRANSFORM (SET, REMOVE, APPEND operations) and JSON_MERGEPATCH change documents; JSON_TABLE only reads.

The workload is mostly relational with a small JSON part. Use columns for the relational part. Oracle 23ai's JSON Relational Duality views let a table be presented as JSON documents with updates through the document, which may fit if you want both models on the same data.

The reference is Oracle's JSON Developer's Guide, including the JSON_TABLE chapter on NESTED PATH and the error clauses described above. The path language follows the SQL/JSON standard, and the JSON grammar behind IS JSON (STRICT) is RFC 8259. A document can be checked by eye in the JSON formatter first.