SQL/JSON Function JSON

17 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. (There is no RETURNING clause.)

As an alternative, you can use the simple dot-notation syntax instead of the first argument and the row path expression. (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 that second argument altogether. These queries are equivalent:

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

json_table(t.j ...)

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.

The mandatory COLUMNS clause defines the columns of the virtual table to be created by json_table. It consists of the keyword COLUMNS followed by the following entries enclosed in parentheses:

At most one entry in the COLUMNS clause can be a column name followed by the keywords FOR ORDINALITY, which specifies a column of generated row numbers (SQL data type NUMBER). These numbers start with one. For example:
```
COLUMNS (linenum FOR ORDINALITY, ProductID)
```
Other than the optional FOR ORDINALITY entry, each entry in the COLUMNS clause is either a regular column specification or a nested columns specification.
A regular column specification consists of a column name followed by an optional scalar data type for the column, which can be SQL data type VARCHAR2, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or SDO_GEOMETRY (the same as for the RETURNING clause of json_value), followed by an optional value clause and an optional PATH clause. The default data type is VARCHAR2(4000).

Data type SDO_GEOMETRY is used for Oracle Spatial and Graph data. In particular, this means that you can use json_table with GeoJSON data, which is a format for encoding geographic data in JSON.
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. This COLUMNS clause specifies columns that represent nested data. The row path expression used here 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.

A COLUMNS clause at any level (nested or not) has the same characteristics. In other words, COLUMNS clause is defined recursively. For each level of nesting (that is, for each use of keyword NESTED), the nested COLUMNS clause is said to be the child of the COLUMNS clause within which it is nested, which is its parent. Two or more COLUMNS clauses that have the same parent clause are 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.

Example 17-1 and Example 17-7 illustrate the use of a nested columns clause.

The only thing required in a regular column specification is the column name. Defining the column projection in more detail, by specifying a scalar data type, value handling, or a target path is optional.

The optional value clause specifies how to handle the data projected to the column: whether to handle it as would json_value, json_exists, or json_query. This value handling includes the return data type, return format (pretty or ASCII), wrapper, and error treatment.

By default, the projected data is handled as if by json_value. If you use keyword EXISTS then it is handled as if by json_exists. If you use keywords FORMAT JSON then it is handled as if by json_query.

For FORMAT JSON you can override the default wrapping behavior by adding an explicit wrapper clause.

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

If the PATH clause is not present then the behavior is the same as if it were present with a path of '$.<column-name>', where <column-name> is the column name. That is, the name of the object field that is targeted is taken implicitly as the column name. For purposes of specifying the targeted field only, the SQL identifier used for <column-name> is interpreted case-sensitively, even if it is not quoted. The SQL name of the column follows the usual rule: if it is enclosed in double quotation marks (") then it the letter case used is significant; otherwise, it is not (it is treated as if uppercase).

For example, these two COLUMNS clauses are equivalent. For SQL, case is significant only for column Comments.
```
COLUMNS(ProductId, quantity NUMBER, "Comments")

COLUMNS(ProductId VARCHAR2(4000) PATH '$.ProductId',
        quantity  NUMBER         PATH '$.quantity',
        "Comments"  VARCHAR2(4000) PATH '$.Comments')
```
Example 17-1 presents equivalent queries that illustrate this.

If you use FORMAT JSON in a given json_table column specification then json_query semantics are used when projecting the JSON data to the column. The data type specified for the column can be any of the SQL data types that json_query can return: VARCHAR2, CLOB, or BLOB.

json_query semantics imply the following:

The projected JSON data is always well-formed. This includes ensuring that non-ASCII characters in string values are escaped as needed. For example, a TAB character (CHARACTER TABULATION, U+0009) is escaped as \t.
json_query error handling applies.
You can use a wrapper clause, to project multiple JSON values as elements in an array.

If you do not use FORMAT JSON in a given json_table column specification then json_value semantics are used when projecting the JSON data. The data type specified for the column can be any of the SQL data types that json_value can return: VARCHAR2, NUMBER, DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, SDO_GEOMETRY, or CLOB. json_value error handling applies (and you cannot use a wrapper clause).

For example, here the value of column FirstName is projected directly using json_value semantics, and the value of column Address is projected as a JSON string using json_query semantics:

COLUMNS (FirstName, Address FORMAT JSON)

You typically use FORMAT JSON when the projected data is a JSON object or array. You typically do not use FORMAT JSON when the projected data is a JSON scalar.

17.1 JSON_TABLE Generalizes SQL/JSON Query Functions and Conditions

SQL/JSON function json_table generalizes SQL/JSON condition json_exists and SQL/JSON functions json_value and json_query. Everything that you can do using these functions you can do using json_table. For the jobs they accomplish, the syntax of these functions is simpler to use than is the syntax of json_table.

If you use any of json_exists, json_value, or json_query more than once, or in combination, to access the same data then a single invocation of json_table presents the advantage that the data is parsed only once.

Because of this, the optimizer often automatically rewrites multiple invocations of json_exists, json_value and json_query (any combination) to fewer invocations of json_table instead, so the data is parsed only once.

Example 17-2 and Example 17-3 illustrate this. They each select the requestor and the set of phones used by each object in column j_purchaseorder.po_document. But Example 17-3 parses that column only once, not four times.

Note the following in connection with Example 17-3:

A JSON value of null is a value as far as SQL is concerned; it is not NULL, which in SQL represents the absence of a value (missing, unknown, or inapplicable data). In Example 17-3, if the JSON value of object attribute zipCode is null then the SQL string 'true' is returned.
json_exists is a SQL condition; you can use it in a SQL WHERE clause, a CASE statement, or a check constraint. In Example 17-2 it is used in a WHERE clause. Function json_table employs the semantics of json_exists implicitly when you specify keyword EXISTS. It must return a SQL value in the virtual column. Since Oracle SQL has no Boolean data type, a SQL string 'true' or 'false' is used to represent the Boolean value. This is the case in Example 17-3: the VARCHAR2 value is stored in column jt.has_zip, and it is then tested explicitly for equality against the literal SQL string 'true'.
JSON field AllowPartialShipment has a JSON Boolean value. When json_value is applied to that value it is returned as a string. In Example 17-3, data type VARCHAR2 is used as the column data type. Function json_table implicitly uses json_value for this column, returning the value as a VARCHAR2 value, which is then tested for equality against the literal SQL string 'true'.

Example 17-2 Accessing JSON Data Multiple Times to Extract Data

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 VARCHAR2(5 CHAR))
        = 'true';

Example 17-3 Using JSON_TABLE to Extract Data Without Multiple Parses

SELECT jt.requestor, jt.phones
  FROM j_purchaseorder,
       json_table(po_document, '$'
         COLUMNS (requestor VARCHAR2(32 CHAR) PATH '$.Requestor',
                  phones    VARCHAR2(100 CHAR) FORMAT JSON
                            PATH '$.ShippingInstructions.Phone',
                  partial   VARCHAR2(5 CHAR) PATH '$.AllowPartialShipment',
                  has_zip   VARCHAR2(5 CHAR) EXISTS
                            PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial = 'true' AND jt.has_zip = 'true';

Related Topics

Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value

Parent topic: SQL/JSON Function JSON_TABLE

17.2 Using JSON_TABLE with JSON Arrays

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

Example 17-4 projects the requestor and associated phone numbers from the JSON data in column po_document. The entire JSON array Phone is projected as a column of JSON data, ph_arr. To format this JSON data as a VARCHAR2 column, the keywords FORMAT JSON are needed.

What if you wanted to project the individual elements of JSON array Phone and not the array as a whole? Example 17-5 shows one way to do this, which you can use if the array elements are the only data you need to project.

If you want to project both the requestor and the corresponding phone data then the row path expression of Example 17-5 ($.Phone[*]) is not appropriate: it targets only the (phone object) elements of array Phone.

Example 17-6 shows one way to target both: use a row path expression that targets both the name and the entire phones array, and use column path expressions that target fields type and number of individual phone objects.

In Example 17-6 as in Example 17-4, keywords FORMAT JSON are needed because the resulting VARCHAR2 columns contain JSON data, namely arrays of phone types or phone numbers, with one array element for each phone. In addition, unlike the case for Example 17-4, a wrapper clause is needed for column phone_type and column phone_num, because array Phone contains multiple objects with fields type and number.

Sometimes you might not want the effect of Example 17-6. For example, you might want a column that contains a single phone number (one row per number), rather than one that contains a JSON array of phone numbers (one row for all numbers for a given purchase order).

To obtain that result, you need to tell json_table to project the array elements, by using a json_table NESTED path clause for the array. A NESTED path clause acts, in effect, as an additional row source (row pattern). Example 17-7 illustrates this.

You can use any number of NESTED keywords in a given json_table invocation.

In Example 17-7 the outer COLUMNS clause is the parent of the nested (inner) COLUMNS clause. The virtual tables defined are joined using an outer join, with the table defined by the parent clause being the outer table in the join.

(If there were a second columns clause nested directly under the same parent, the two nested clauses would be sibling COLUMNS clauses.)

Example 17-4 Projecting an Entire JSON Array as JSON Data

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

Example 17-5 Projecting Elements of a JSON Array

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";

PHONE_TYPE     PHONE_NUM
----------     ---------
Office         909-555-7307
Mobile         415-555-1234

Example 17-6 Projecting Elements of a JSON Array Plus Other Data

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

REQUESTOR    PHONE_TYPE            PHONE_NUM
---------    ----------            ---------
Alexis Bull  ["Office", "Mobile"]  ["909-555-7307", "415-555-1234"]

Example 17-7 JSON_TABLE: Projecting Array Elements Using NESTED

This example shows two equivalent queries that project array elements. 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 number, whose SQL identifier is quoted ("number"), the SQL column names are, in effect, uppercase. (Column number is lowercase.)

In the first query the column names are written exactly the same as the field names that are targeted, including with respect to letter case. Regardless of whether they are quoted, they are interpreted case-sensitively for purposes of establishing the proper path.

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.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";

Related Topics

Parent topic: SQL/JSON Function JSON_TABLE

17.3 Creating a View Over JSON Data Using JSON_TABLE

To improve query performance you can create a view over JSON data that you project to columns using SQL/JSON function json_table. To further improve query performance you can create a materialized view and place the JSON data in memory.

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

Example 17-9 defines a materialized view that has the same data and structure as Example 17-8. You cannot update such a materialized view directly; you must treat it as a read-only view and update it indirectly by updating the base table. An error is raised if you try to modify the view directly. If the materialized view is created using keywords REFRESH and ON STATEMENT then the view is updated automatically whenever you update the base table.

The only differences between Example 17-8 and Example 17-9 are:

The use of keyword MATERIALIZED.
The use of BUILD IMMEDIATE.
The use of REFRESH FAST ON STATEMENT WITH PRIMARY KEY.

The use of REFRESH FAST means that the materialized view will be refreshed incrementally. For this to occur, you must use either WITH PRIMARY KEY or WITH ROWID (if there is no primary key). Oracle recommends that you specify a primary key for a table that has a JSON column and that you use WITH PRIMARY KEY when creating a materialized view based on it.

You could use ON COMMIT in place of ON STATEMENT for the view creation. The former synchronizes the view with the base table only when your table-updating transaction is committed. Until then the table changes are not reflected in the view. If you use ON STATEMENT then the view is immediately synchronized after each DML statement. This also means that a view created using ON STATEMENT reflects any rollbacks that you might perform. (A subsequent COMMIT statement ends the transaction, preventing a rollback.)