JSON_TABLE Function

The JSON_TABLE SQL/JSON function projects specific JSON data to columns of various SQL data types. It maps parts of a JSON document into the rows and columns of a new, virtual table. You can then insert this virtual table into a pre-existing table, or you can query it using SQL—in a join expression, for example.

A common use of JSON_TABLE is to create a non-materialized view of JSON data. You can use such a view just as you would use any table or view. This lets applications operate on JSON data without consideration of JSON syntax or path expressions.

Defining a view over JSON data in effect maps a kind of schema onto that data. This mapping is after the fact: the underlying JSON data can be defined and created without any regard to a schema or any particular pattern of use. Data first, schema later. Such mapping imposes no restriction on the kind of JSON documents that can be stored in the database (other than being well-formed JSON data). The view exposes only data that conforms to the schema that defines the view. To change the schema, just redefine the view—no need to reorganize the underlying JSON data.

You use JSON_TABLE in a FROM clause. It is a row source: it generates a row of virtual-table data for each JSON value selected by a row path expression (row pattern). The columns of each generated row are defined by the column path expressions of the COLUMNS clause.

Typically a JSON_TABLE invocation is laterally joined, implicitly, with a source table in the FROM list, whose rows each contain a JSON document that is used as input to the function. JSON_TABLE generates zero or more new rows, as determined by evaluating the row path expression against the input document.

JSON_TABLE has two required arguments, and it accepts some optional clauses.

The first argument to JSON_TABLE is a SQL expression. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting—it, however, cannot be a SELECT query. The result of evaluating the expression is used as the context item for evaluating the row path expression.
The second argument to JSON_TABLE is the SQL/JSON row path expression followed by an optional error clause for handling the row and the required COLUMNS clause, which defines the columns of the virtual table to be created.

There are two levels of error handling for JSON_TABLE, corresponding to the two levels of path expressions: row and column. When present, a column error handler overrides row-level error handling. The default error handler for both levels is NULL ON ERROR.

If the ON EMPTY clause is present, then the ON ERROR clause also handles cases where the targeted JSON field is missing.

If the ON MISMATCH clause is present, then the ON ERROR clause also handles cases where a targeted JSON field type does not match the return type or if there is missing or extra data.

As an alternative to passing the context-item argument and the row path expression, you can use simple dot-notation syntax. You can still use an error clause and the COLUMNS clause is still required. Dot notation specifies a table or view column together with a simple path to the targeted JSON data. For example, these two queries are equivalent:

JSON_TABLE(po.po_document, '$.ShippingInstructions.Phone[*]' ...)

JSON_TABLE(po.po_document.ShippingInstructions.Phone[*] ...)

And in cases where the row path expression is only '$', which targets the entire JSON document, you can omit the path part. These two queries are equivalent:

JSON_TABLE(po.po_document, '$' ...)

JSON_TABLE(po.po_document ...)

You can also use the dot notation in any PATH clause of a COLUMNS clause, as an alternative to using a SQL/JSON path expression. For example, you can use just PATH 'ShippingInstructions.name' instead of PATH '$.ShippingInstructions.name'.

COLUMNS Clause of JSON_TABLE

The COLUMNS clause for the JSON_TABLE function defines the columns of the virtual table that the function creates.

The clause consists of the COLUMNS keyword followed, enclosed in parenthesis, by the following entries:

Note:

Other than the optional FOR ORDINALITY entry, each entry in the COLUMNS clause is either a regular column specification or a nested columns specification.

At most, one entry can be a column name followed by the FOR ORDINALITY keywords, which specifies a column of generated row numbers (SQL NUMBER data type). These numbers start with one.
```
COLUMNS(itemNum FOR ORDINALITY, Quantity)
```
An array step in a row path expression can lead to any number of rows that match the path expression. In particular, the order of the array-step indexes and ranges, multiple occurrences of an array index, and duplication of a specified position due to range overlap produce one row for each position match. The ordinality row numbers reflect this.
A regular column specification consists of a column name followed by an optional data type for the column, which can be any SQL data type that can be used in the RETURNING clause of JSON_VALUE, followed by an optional value clause, and an optional PATH clause. The default data type is VARCHAR2(4000).

The column data type can be any of these: BINARY_DOUBLE, BINARY_FLOAT, BOOLEAN, CHAR, CLOB, DATE, DOUBLE PRECISION, FLOAT, INTEGER, NUMBER, NCHAR, NCLOB, NVARCHAR2, RAW, REAL, TIMESTAMP, and VARCHAR2. You can also use TimesTen data types: TT_BIGINT, TT_CHAR, TT_DATE, TT_INTEGER, TT_NCHAR, TT_NVARCHAR, TT_SMALLINT, TT_TIMESTAMP, TT_TINYINT, and TT_VARCHAR.

The SQL/JSON standard is extended to allow the TRUNCATE optional keyword immediately after a character data type. This is in case the returning value is wider than the length (N) specified for the column data type. When TRUNCATE is present and the value to return is wider than N, then the value is truncated—only the first N characters are returned. If TRUNCATE is absent, then this case is treated as an error, handled as usual by the specified error-handling behavior.
A nested columns specification consists of the keyword NESTED followed by an optional PATH keyword, a SQL/JSON row path expression, and then a COLUMNS clause. The COLUMNS clause specifies columns that represent nested data. The row path expression provides a refined context for the specified nested columns: each nested column path expression is relative to the row path expression. You can nest COLUMNS clauses to project values that are present in arrays at different levels to columns of the same row.

The COLUMNS clause is defined recursively. For each use of the NESTED keyword, the nested COLUMNS clause is considered a child of the COLUMNS clause within which it is nested, the parent. Two or more COLUMNS clauses that have the same parent clause are considered siblings. The virtual tables defined by parent and child COLUMNS clauses are joined using an outer join, with the parent being the outer table. The virtual columns defined by sibling COLUMNS clauses are joined using a UNION join. See Example 3-18 and Example 3-25.

A regular columns specification only requires the column name. The scalar data type, value handling, or target path used to define the column projection in more detail are optional.

The optional value clause specifies whether data projected to the column is handled as would JSON_EXISTS, JSON_QUERY, or JSON_VALUE. This value handling includes the return data type, return format (pretty or ASCII), wrapper, and error treatment.
- If you use the EXISTS keyword, then the projected data is handled as if by JSON_EXISTS, regardless of the column data type.
- For a column of the JSON data type, the projected data is handled as if by JSON_QUERY.
- For a column of a non-JSON data type, the projected data is handled by default as if by JSON_VALUE. However, if you use the FORMAT JSON keywords, then the projected data is handled as if by JSON_QUERY. You typically use FORMAT JSON only when the projected data is a JSON object or array.
For example, here the value of the name column is projected directly using JSON_VALUE semantics, and the value of the Address column is projected as a JSON string using JSON_QUERY semantics:
```
COLUMNS (name, Address FORMAT JSON)
```
When the column uses JSON_QUERY semantics, you can override the default wrapping behavior by adding an explicit wrapper clause.

You can override the default error handling for a given handler (JSON_EXISTS, JSON_QUERY, or JSON_VALUE) by adding an appropriate explicit error clause.
The optional PATH clause specifies the portion of the row that is to be used as the column content. The column path expression following the PATH keyword is matched against the context item provided by the virtual row. The column path expression must represent a relative path to the path specified by the row path expression.

If the PATH clause is omitted, then the behavior is the same as PATH '$.<column_name>', where <column_name> is the column name. The name of the targeted field is taken implicitly as the column name. The SQL identified used for <column_name> is case-sensitive only for the purpose of identifying the target field. For example, these two JSON_TABLE expressions are equivalent:
```
COLUMNS(PONumber NUMBER, "User", CostCenter)

COLUMNS(ponumber   NUMBER         PATH '$.PONumber', 
        "user"     VARCHAR2(4000) PATH '$.User', 
        costcenter VARCHAR2(4000) PATH '$.CostCenter')
```
Note:

USER is a reserved word. The double-quotation marks are needed to avoid an error.

Example 3-18 presents equivalent queries that illustrate this.

You can also use dot notation in a PATH clause, as an alternation to SQL/JSON path expression. See Example 3-19 and Example 3-25.

In a column path-expression array step, the order of indexes and ranges, multiple occurrences of an array index, and duplication of a specified position due to range overlaps have the same effect as they would have for the particular semantics use of the column.

JSON_EXISTS: Only checks for the set of specified positions (at least one of each), not for the order or the number of times specified.
JSON_QUERY: Each occurrence of a specified position is matched against the data, in order.
JSON_VALUE: If only one position is specified, then it is matched against the data. Otherwise, there is no match and a SQL NULL is returned, by default.

A columns clause with JSON_VALUE semantics also accepts the optional TYPE (STRICT) keywords following the PATH clause. This behaves as when used in the RETURNING clause of JSON_VALUE. For example, in this query, only PONumber fields whose value is numeric are projected.

SELECT jt.ponum FROM j_purchaseorder,
  JSON_TABLE(po_document, '$'
    COLUMNS(ponum NUMBER PATH '$.PONumber' TYPE (STRICT))
  ) AS "JT";

Using a NESTED Clause Instead of JSON_TABLE

In a SELECT statement, you can often use a SQL NESTED clause instead of a JSON_TABLE function. In addition to being a simpler query expression, it has the advantage of including rows with non-NULL relational columns when the JSON column is NULL.

The NESTED clause is a shortcut for using JSON_TABLE with a LEFT OUTER JOIN. These two queries are equivalent:.

SELECT ... FROM table_name
  NESTED json_column COLUMNS (...);

SELECT ... FROM table_name ta
  LEFT OUTER JOIN JSON_TABLE(ta.json_column COLUMNS (...)
    ON 1 = 1;

A LEFT OUTER JOIN with JSON_TABLE (or the NESTED clause) allows the result to include rows with no corresponding data from the JSON type column—in other words, where the JSON column is NULL.

The NESTED clause requires the same COLUMNS clause as JSON_TABLE, including the possibility of nested columns. These are the advantages of using the NESTED clause:

A table alias is not required, even if you use simple dot notation.
LEFT OUTER JOIN is implicit.

Example 3-19 SQL NESTED and JSON_TABLE with LEFT OUTER JOIN

The two queries in this example are equivalent. The first query uses JSON_TABLE with an explicit LEFT OUTER JOIN. The second query uses a NESTED clause.

SELECT id, requestor, type, number FROM j_purchaseorder 
  LEFT OUTER JOIN JSON_TABLE(po_document
    COLUMNS (Requestor,
             NESTED ShippingInstructions.Phone[*]
               COLUMNS (type, "number")))
    ON 1 = 1;

SELECT id, requestor, type, number FROM j_purchaseorder 
  NESTED po_document
    COLUMNS (Requestor,
             NESTED ShippingInstructions.Phone[*]
               COLUMNS (type, "number"));

Note:

The "number" column specification requires double-quotation marks because NUMBER is a keyword.

Both queries return this output, given the JSON data inserted into the j_purchaseorder table in Inserting and Updating JSON Data.

< 0, Alberto Errazuriz, Office, 57-555-983 >
< 1, Alexis Bull, Office, 909-555-7307 >
< 1, Alexis Bull, Mobile, 415-555-1234 >
3 rows found.

Example 3-20

The query in this example selects the id and date_loaded columns from the j_purchaseorder table, along with the array elements of the Phone field—which is nested in the value of the ShippingInstructions field of the JSON type column, po_document. The query also expands the Phone array value as the type and number columns.

SELECT * FROM j_purchaseorder 
  NESTED po_document.ShippingInstructions.Phone[*]
    COLUMNS (type, "number");

Note:

The "number" column specification requires double-quotation marks because NUMBER is a keyword.

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Inserting and Updating JSON Data.

< 0, 2025-08-05 16:55:27.000000, Office, 57-555-983 >
< 1, 2025-08-05 16:55:29.000000, Office, 909-555-7307 >
< 1, 2025-08-05 16:55:29.000000, Mobile, 415-555-1234 >
3 rows found.

Using JSON_TABLE Instead of Other SQL/JSON Functions or Conditions

The JSON_TABLE function generalizes the JSON_EXISTS condition and JSON_VALUE and JSON_QUERY functions. Everything you can do using the latter functions and condition, you can do using JSON_TABLE. However, given their capabilities, the syntax of these functions is simpler than the syntax of JSON_TABLE.

If you would use any of JSON_EXISTS, JSON_VALUE, or JSON_QUERY more than once—or use them in combination—to access the same JSON data, then you can use a single invocation of JSON_TABLE instead. This can often make the query more readable, and it ensures that the query is optimized to read the data once. Example 3-21 illustrates two equivalent queries.

Example 3-21 JSON/SQL Functions and Condition Expressed Using JSON_TABLE

The queries in this example are equivalent. Both SELECT statements have the same effect.

This query reads the po_document JSON column four times, since it uses four invocations of SQL/JSON functions to access the column.

SELECT JSON_VALUE(po_document, '$.Requestor' RETURNING VARCHAR2(32)),
       JSON_QUERY(po_document, '$.ShippingInstructions.Phone'
                  RETURNING VARCHAR2(100))
  FROM j_purchaseorder
  WHERE JSON_EXISTS(po_document, '$.ShippingInstructions.Address.zipCode')
    AND JSON_VALUE(po_document,  '$.AllowPartialShipment'
                   RETURNING BOOLEAN) = 'TRUE';

This query reads the po_document JSON column once, since it uses a single invocation of JSON_TABLE to access the column.

SELECT jt.requestor, jt.phones FROM j_purchaseorder,
  JSON_TABLE(po_document, '$'
    COLUMNS (
      requestor VARCHAR2(32) PATH '$.Requestor',
      phones    VARCHAR2(100) FORMAT JSON
                PATH '$.ShippingInstructions.Phone',
      partial   BOOLEAN PATH '$.AllowPartialShipment',
      has_zip   BOOLEAN EXISTS
                PATH '$.ShippingInstructions.Address.zipCode')
  ) AS "JT"
  WHERE jt.partial = 'TRUE' AND jt.has_zip = 'TRUE';

Note:

These queries use SQL BOOLEAN values to represent JSON Boolean values of the AllowPartialShipment field. However, TimesTen does not support the SQL BOOLEAN data type, so all SQL BOOLEAN values are mapped as VARCHAR2(7) values, TRUE or FALSE.

A JSON null is a value as far as SQL is concerned. It is not NULL, which in SQL represents the absence of a value. In these queries, if the JSON value of the zipCode field is null, then they return a TRUE SQL BOOLEAN value.

Both queries return this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< Alexis Bull, [{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-5
55-1234"}] >
1 row found.

Using JSON_TABLE with JSON Arrays

A JSON value can be an array or can include one or more arrays, to any number of levels inside other JSON arrays or objects. You can use JSON_TABLE with a NESTED PATH clause to project specific elements of an array.

The following examples show different ways in which you can use JSON_TABLE to project an entire JSON array or individual elements of a JSON array.

Example 3-22 Projecting an Entire JSON Array as JSON Data

The query in this example projects the Requestor field and associated Phone JSON array from the JSON data in the po_document column. The Phone array is projected as a column of JSON data, ph_arr.

The query uses the FORMAT JSON keywords to format the Phone array as a VARCHAR2 column.

SELECT jt.* FROM j_purchaseorder,
   JSON_TABLE(po_document, '$'
     COLUMNS (requestor VARCHAR2(32) PATH '$.Requestor',
              ph_arr    VARCHAR2(100) FORMAT JSON
                        PATH '$.ShippingInstructions.Phone')
   ) AS "JT";

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< Alberto Errazuriz, [{"type":"Office","number":"57-555-983"}] >
< Alexis Bull, [{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}] >
2 rows found.

Example 3-23 Projecting Elements of a JSON Array

The query of this example projects individual elements of the Phone JSON array. The use of an array step in the row path expression only applies if the elements of the array are the only data you need to project.

SELECT jt.* FROM j_purchaseorder,
  JSON_TABLE(po_document, '$.ShippingInstructions.Phone[*]'
    COLUMNS (phone_type VARCHAR2(10) PATH '$.type',
             phone_num  VARCHAR2(20) PATH '$.number')
  ) AS "JT";

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< Office, 57-555-983 >
< Office, 909-555-7307 >
< Mobile, 415-555-1234 >
3 rows found.

Example 3-24 Projecting Elements of a JSON Array Plus Other Data

The query of this example projects the Requestor field and the type and number fields of every element of the Phone JSON array. This query uses a row path expression that targets both the Requestor field and Phone array, and then uses column path expressions to target the type and number fields of individual objects in the Phone array.

The query uses the FORMAT JSON keywords and WRAPPER clause to format the multiple objects with type and number fields as VARCHAR2 columns, phone_type and phone_num respectively.

SELECT jt.* FROM j_purchaseorder,
  JSON_TABLE(po_document, '$'
    COLUMNS (
      requestor  VARCHAR2(32) PATH '$.Requestor',
      phone_type VARCHAR2(50) FORMAT JSON WITH WRAPPER
                 PATH '$.ShippingInstructions.Phone[*].type',
      phone_num  VARCHAR2(50) FORMAT JSON WITH WRAPPER
                 PATH '$.ShippingInstructions.Phone[*].number')
  ) AS "JT";

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< Alberto Errazuriz, ["Office"], ["57-555-983"] >
< Alexis Bull, ["Office","Mobile"], ["909-555-7307","415-555-1234"] >
2 rows found.

Example 3-25 Projecting Array Elements Using NESTED

The queries in this example are equivalent. The first query uses simple dot-notation syntax for the row and column path expressions. The second query uses full syntax.

These queries project the same data as Example 3-24. However, instead of using a single row for the multiple objects with type and number fields in the Phone array, these queries use a single row for each object with those fields in the array.

To this effect, the queries use the NESTED path clause to project the array elements, where the NESTED path clause acts as an additional row pattern. The outer COLUMNS clause is the parent of the nested COLUMNS clause. The virtual tables defined are joined using an outer join, with the table defined by the parent clause being the outer table of the join. If there were a second COLUMNS clause nested directly under the same parent, the two nested clauses would be sibling COLUMNS clauses.

SELECT jt.* FROM j_purchaseorder po,
  JSON_TABLE(po.po_document
    COLUMNS (Requestor,
             NESTED ShippingInstructions.Phone[*]
              COLUMNS (type, "number"))
  ) AS "JT";

SELECT jt.* FROM j_purchaseorder po,
  JSON_TABLE(po.po_document, '$'
    COLUMNS (Requestor VARCHAR2(4000) PATH '$.Requestor',
             NESTED PATH '$.ShippingInstructions.Phone[*]'
               COLUMNS (type     VARCHAR2(4000) PATH '$.type',
                        "number" VARCHAR2(4000) PATH '$.number'))
  ) AS "JT";

Both queries return this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< Alberto Errazuriz, Office, 57-555-983 >
< Alexis Bull, Office, 909-555-7307 >
< Alexis Bull, Mobile, 415-555-1234 >
3 rows found.

Creating a View with JSON_TABLE

To improve query performance, you can create a view over JSON data that you project to columns using the JSON_TABLE function. To further improve query performance, you can create a materialized view, where the resulting JSON data has already been calculated from the detail tables.

Example 3-26 defines a materialized view over JSON data. It uses the NESTED path clause to project elements of the LineItems array.