SQL/JSON Function JSON

21 SQL/JSON Function JSON_TABLE

SQL/JSON function json_table projects specific JSON data to columns of various SQL data types. You use it to map parts of a JSON document into the rows and columns of a new, virtual table, which you can also think of as an inline view.

You can then insert this virtual table into a pre-existing database table, or you can query it using SQL — in a join expression, for example.

A common use of json_table is to create a view of JSON data. You can use such a view just as you would use any table or view. This lets applications, tools, and programmers operate on JSON data without consideration of the syntax of JSON or JSON 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 a schema (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 mapping (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 SQL 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.

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. 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 is no RETURNING clause.

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.

In a row 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 all have the usual effect: the specified positions are matched, in order, against the data, producing one row for each position match.

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(t.j, '$.ShippingInstructions.Phone[*]' ...)

json_table(t.j.ShippingInstructions.Phone[*] ...)

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

json_table(t.j, '$' ...)

json_table(t.j ...)

Example 21-1 illustrates the difference between using the simple dot notation and using the fuller, more explicit notation.

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'.

Example 21-1 Equivalent JSON_TABLE Queries: Simple and Full Syntax

This example uses json_table for two equivalent queries. The first query uses the simple, dot-notation syntax for the expressions that target the row and column data. The second uses the full syntax.

Except for column Special Instructions, whose SQL identifier is quoted, the SQL column names are, in effect, uppercase. (Identifier Special Instructions contains a space character.)

In the first query the column names are written exactly the same as the names of the targeted object fields, including with respect to letter case. Regardless of whether they are quoted, they are interpreted case-sensitively for purposes of establishing the default path (the path used when there is no explicit PATH clause).

The second query has:

Separate arguments of a JSON column-expression and a SQL/JSON row path-expression
Explicit column data types of VARCHAR2(4000)
Explicit PATH clauses with SQL/JSON column path expressions, to target the object fields that are projected

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data
         COLUMNS ("Special Instructions",
                  NESTED LineItems[*]
                    COLUMNS (ItemNumber NUMBER,
                             Description PATH Part.Description))
       ) AS "JT";

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data, 
         '$'
         COLUMNS (
           "Special Instructions" VARCHAR2(4000)
                                  PATH '$."Special Instructions"',
           NESTED PATH '$.LineItems[*]'
             COLUMNS (
               ItemNumber  NUMBER        PATH '$.ItemNumber',
               Description VARCHAR(4000) PATH '$.Part.Description'))
       ) AS "JT";

_________________________________________________________