SQL/JSON Function JSON

17 SQL/JSON Function JSON_TABLE

SQL/JSON function json_table projects specific JSON data into columns of various SQL data types. You use it to decompose the result of JSON expression evaluation 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 underlying table (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 data for each JSON value selected by a row path expression (row pattern).

The rows created by a json_table invocation are laterally joined, implicitly, to the row that generated them. That is, you need not explicitly join the virtual table produced by json_table with the table that contains the JSON data.

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

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.

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 a SQL/JSON row path expression followed by an optional error clause for handling the row and a (required) COLUMNS clause. (There is no RETURNING clause.) The path expression can target any number of JSON values.

The row path expression acts as a pattern for the rows of the generated virtual table. It is matched against the context item provided by the SQL FROM clause, producing rows of SQL data that are organized into columns, which you specify in the COLUMNS clause. Each of those rows is matched against zero or more column path expressions to generate the columns of the virtual table.

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.
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 a mandatory 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.

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-6 illustrates the use of a nested columns clause.
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 mandatory 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.

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-1 and Example 17-2 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-2 parses that column only once, not four times.

Note the following in connection with Example 17-2:

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-2, if the JSON value of object attribute zipCode is null then the SQL string 'true' is returned.
Although json_exists returns a Boolean value, as a SQL value this is represented by the SQL string 'true' or 'false'. If json_exists is used directly as a condition in a SQL WHERE clause or CASE statement then you need not test this return value explicitly; you can simply write json_exists(...). But if json_exists is used elsewhere, to obtain a value, then the only way to test that value is as an explicit string. That is the case in Example 17-2: the value is stored in column jt.has_zip, and it is then tested explicitly for equality against the SQL string 'true'.
The JSON object attribute AllowPartialShipment has a JSON Boolean value. When json_value is applied to that value it can be returned as either a string or a number. In Example 17-2, data type NUMBER is used as the column data type. Function json_table implicitly uses json_value for this column, returning the value as a number, which is then tested for equality against the number 1.

Example 17-1 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 NUMBER(1))
        = 1;

Example 17-2 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   NUMBER(1) PATH '$.AllowPartialShipment',
                  has_zip   VARCHAR2(5 CHAR) EXISTS
                            PATH '$.ShippingInstructions.Address.zipCode')) jt
  WHERE jt.partial = 1 AND has_zip = 'true';

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-3 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-4 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-4 ($.Phone[*]) is not appropriate: it targets only the (phone object) elements of array Phone.

Example 17-5 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-5 as in Example 17-3, 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-3, 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-5. 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-6 illustrates this.

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

In Example 17-6 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-3 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-4 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-5 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-6 JSON_TABLE: Projecting Array Elements Using NESTED

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

Related Topics

Creating a View Over JSON Data Using 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 read-only materialized view and place the JSON data in memory.

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

Example 17-8 defines a materialized view that has the same data and structure as Example 17-7. You cannot use such a materialized view for update; you must treat it as a read-only view. An error is raised if you try to modify it.

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

The use of keyword MATERIALIZED.
The use of BUILD IMMEDIATE.
The use of REFRESH FAST ON COMMIT 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.

See Also:

Oracle Database Data Warehousing Guide

Example 17-7 Creating a View Over JSON Data

CREATE OR REPLACE VIEW j_purchaseorder_detail_view
  AS SELECT d.*
       FROM j_purchaseorder po,
            json_table(po.po_document, '$'
              COLUMNS (
                po_number        NUMBER(10)         PATH '$.PONumber',
                reference        VARCHAR2(30 CHAR)  PATH '$.Reference',
                requestor        VARCHAR2(128 CHAR) PATH '$.Requestor',
                userid           VARCHAR2(10 CHAR)  PATH '$.User',
                costcenter       VARCHAR2(16)       PATH '$.CostCenter',
                ship_to_name     VARCHAR2(20 CHAR)
                                 PATH '$.ShippingInstructions.name',
                ship_to_street   VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.street',
                ship_to_city     VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.city',
                ship_to_county   VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.county',
                ship_to_postcode VARCHAR2(10 CHAR)
                                 PATH '$.ShippingInstructions.Address.postcode',
                ship_to_state    VARCHAR2(2 CHAR)
                                 PATH '$.ShippingInstructions.Address.state',
                ship_to_zip      VARCHAR2(8 CHAR)
                                 PATH '$.ShippingInstructions.Address.zipCode',
                ship_to_country  VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.country',
                ship_to_phone    VARCHAR2(24 CHAR)
                                 PATH '$.ShippingInstructions.Phone[0].number',
                NESTED PATH '$.LineItems[*]'
                  COLUMNS (
                    itemno      NUMBER(38)         PATH '$.ItemNumber', 
                    description VARCHAR2(256 CHAR) PATH '$.Part.Description', 
                    upc_code    VARCHAR2(14 CHAR)  PATH '$.Part.UPCCode', 
                    quantity    NUMBER(12,4)       PATH '$.Quantity', 
                    unitprice   NUMBER(14,2)       PATH '$.Part.UnitPrice'))) d;

Example 17-8 Creating a Materialized View Over JSON Data

CREATE OR REPLACE MATERIALIZED VIEW j_purchaseorder_materialized_view
  BUILD IMMEDIATE
  REFRESH FAST ON COMMIT WITH PRIMARY KEY
  AS SELECT d.*
       FROM j_purchaseorder po,
            json_table(po.po_document, '$'
              COLUMNS (
                po_number        NUMBER(10)         PATH '$.PONumber',
                reference        VARCHAR2(30 CHAR)  PATH '$.Reference',
                requestor        VARCHAR2(128 CHAR) PATH '$.Requestor',
                userid           VARCHAR2(10 CHAR)  PATH '$.User',
                costcenter       VARCHAR2(16)       PATH '$.CostCenter',
                ship_to_name     VARCHAR2(20 CHAR)
                                 PATH '$.ShippingInstructions.name',
                ship_to_street   VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.street',
                ship_to_city     VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.city',
                ship_to_county   VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.county',
                ship_to_postcode VARCHAR2(10 CHAR)
                                 PATH '$.ShippingInstructions.Address.postcode',
                ship_to_state    VARCHAR2(2 CHAR)
                                 PATH '$.ShippingInstructions.Address.state',
                ship_to_zip      VARCHAR2(8 CHAR)
                                 PATH '$.ShippingInstructions.Address.zipCode',
                ship_to_country  VARCHAR2(32 CHAR)
                                 PATH '$.ShippingInstructions.Address.country',
                ship_to_phone    VARCHAR2(24 CHAR)
                                 PATH '$.ShippingInstructions.Phone[0].number',
                NESTED PATH '$.LineItems[*]'
                  COLUMNS (
                    itemno      NUMBER(38)         PATH '$.ItemNumber', 
                    description VARCHAR2(256 CHAR) PATH '$.Part.Description', 
                    upc_code    VARCHAR2(14 CHAR)  PATH '$.Part.UPCCode', 
                    quantity    NUMBER(12,4)       PATH '$.Quantity', 
                    unitprice   NUMBER(14,2)       PATH '$.Part.UnitPrice'))) d;

Related Topics

Using JSON_TABLE with JSON Arrays