39 JSON in Oracle Database

Overview of JSON Syntax and the Data It Represents

A JSON value is one of the following: object, array, number, string, true, false, or null. All values except objects and arrays are scalar.

A JavaScript object is an associative array, or dictionary, of zero or more pairs of property names and associated JSON values.^Foot 2  A JSON object is a JavaScript object literal.^Foot 3  It is written as such a property list enclosed in braces ({, }), with name-value pairs separated by commas (,), and with the name and value of each pair separated by a colon (:).

In JSON each property name and each string value must be enclosed in double quotation marks ("). In JavaScript notation, a property name used in an object literal can be, but need not be, enclosed in double quotation marks. It can also be enclosed in single quotation marks (').

As a result of this difference, in practice, data that is represented using unquoted or single-quoted property names is sometimes referred to loosely as being represented in JSON, and some implementations of JSON, including the Oracle Database implementation, support the lax syntax that allows the use of unquoted and single-quoted property names.

A string in JSON is composed of Unicode characters, with backslash (\) escaping. A JSON number (numeral) is represented in decimal notation, possibly signed and possibly including a decimal exponent.

An object property is sometimes called a key. An object property name-value pair is sometimes called an object member. Order is not significant among object members.

A JavaScript array has zero or more elements. In JSON an array is represented by brackets ([, ]) surrounding the representations of the array elements, which are separated by commas (,), and each of which is an object, an array, or a scalar value. Array element order is significant.

Example 39-1 shows a JSON object that represents a purchase order, with top-level property names PONumber, Reference, Requestor, User, Costcenter, ShippingInstruction, Special Instructions, AllowPartialShipment and LineItems.

{ "PONumber"             : 1600,
  "Reference"            : "ABULL-20140421",
  "Requestor"            : "Alexis Bull",
  "User"                 : "ABULL",
  "CostCenter"           : "A50",
  "ShippingInstructions" : { "name"   : "Alexis Bull",
                             "Address": { "street"  : "200 Sporting Green",
                                          "city"    : "South San Francisco",
                                          "state"   : "CA",
                                          "zipCode" : 99236,
                                          "country" : "United States of America" },
                             "Phone" : [ { "type" : "Office", "number" : "909-555-7307" },
                                         { "type" : "Mobile", "number" : "415-555-1234" } ] },
  "Special Instructions" : null,
  "AllowPartialShipment" : false,
  "LineItems"            : [ { "ItemNumber" : 1,
                               "Part"       : { "Description" : "One Magic Christmas",
                                                "UnitPrice"   : 19.95,
                                                "UPCCode"     : 13131092899 },
                               "Quantity"   : 9.0 },
                             { "ItemNumber" : 2,
                               "Part"       : { "Description" : "Lethal Weapon",
                                                "UnitPrice"   : 19.95,
                                                "UPCCode"     : 85391628927 },
                               "Quantity"   : 5.0 } ] }

• Most of the properties have string values. For example: property User has value "ABULL".

• Properties PONumber and zipCode have numeric values: 1600 and 99236.

• Property Shipping Instructions has an object as value. This object has three members, with properties name, Address, and Phone. Property name has a string value ("Alexis Bull"). Properties Address and Phone each have an object value.

• The value of property Address is an object with properties street, city, state, zipCode, and country. Property zipCode has a numeric value; the others have string values.

• Property Phone has an array as value. This array has two elements, each of which represents an object literal. Each of these objects has two members: properties type and number and their values.

• Property Special Instructions has a null value.

• Property AllowPartialShipment has the Boolean value true.

• Property LineItems has an array as value. This array has two elements, each of which is an object. Each of these objects has three members, with keys ItemNumber, Part, and Quantity.

• Properties ItemNumber and Quantity have numeric values. Property Part has an object as value, with properties Description, UnitPrice, and UPCCode. Property Description has a string value. Properties UnitPrice and UPCCode have numeric values.

Overview of JSON Compared with XML

Both JSON and XML (Extensible Markup Language) are commonly used as data-interchange languages. Here is a brief list of some of their differences.

• JSON data types are few and predefined. XML data can be either typeless or based on an XML schema or a document type definition (DTD).

• JSON has simple structure-defining and document-combining constructs: it lacks attributes, namespaces, inheritance and substitution.

• The order of the members of a JavaScript object literal is insignificant. In general, order matters within an XML document.

• JSON lacks an equivalent of XML text nodes (XPath node test text()). In particular, this means that there is no mixed content.

JSON is most useful with simple, structured data. XML is useful for both structured and semi-structured data. JSON is generally data-centric, not document-centric; XML can be either. JSONis not a markup language; it is designed only for data representation. XML is both a document markup language and a data representation language.

Because of its simple definition and features, JSON data is generally easier to generate, parse, and process than XML data. Use cases that involve combining different data sources generally lend themselves well to the use of XML, because it offers namespaces and other constructs facilitating modularity and inheritance.

JSON, unlike XML (and unlike JavaScript), has no date data type. A date is represented in JSON using the available data types, such as string. There are some de facto standards for converting between real dates and strings. But programs using JSON must, one way or another, deal with date representation conversion.

Getting Started Using JSON with Oracle Database

In general, you will perform the following tasks when working with JSON data in Oracle Database:

1. Create a relational table with a JSON column, and add an is json check constraint to ensure that the column contains only well-formed JSON data.

   The following statement creates relational table j_purchaseorder with JSON column po_document (see also Example 39-3):

   CREATE TABLE j_purchaseorder
      (id          RAW (16) NOT NULL,
       date_loaded TIMESTAMP WITH TIME ZONE,
       po_document CLOB
       CONSTRAINT ensure_json CHECK (po_document IS JSON));
   

2. Insert JSON data into the JSON column, using any of the methods available for Oracle Database.

   The following statement uses a SQL INSERT statement to insert some simple JSON data. Some of the data is elided here (...). See Example 39-4 for these details.

   INSERT INTO j_purchaseorder
     VALUES (SYS_GUID(),
             SYSTIMESTAMP,
             '{"PONumber"             : 1600,
               "Reference"            : "ABULL-20140421",
               "Requestor"            : "Alexis Bull",
               "User"                 : "ABULL",
               "CostCenter"           : "A50",
               "ShippingInstructions" : {...},
               "Special Instructions" : null,
               "AllowPartialShipment" : true,
               "LineItems"            : [...]}');
   

3. Query the JSON data. The return value is always a VARCHAR2 instance that represents a JSON value. Here are some simple examples.

   The following query extracts, from each JSON document in column po_document, a scalar value, the JSON number that is the value of property PONumber for the objects in JSON column po_document (see also Example 39-18):

   
   SELECT po.po_document.PONumber FROM j_purchaseorder po;
   

   The following query extracts, from each document in JSON column po_document, an array of JSON phone objects, which is the value of object property Phone of the value of object property ShippingInstructions (see also Example 39-19):

   
   SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;
   

   The following query extracts, from each JSON document, multiple values as an array: the value of object property type for each object in array Phone. The returned array is not part of the stored data but is constructed automatically by the query. (The order of the array elements is unspecified.)

   
   SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;
   

Escape of Unicode Characters in JSON Data

ASCII characters correspond directly to the first 128 Unicode characters. If your application can use an ASCII escape sequence to represent input Unicode characters that might otherwise require character-set conversion, then you will avoid any possible performance penalty or information loss from such conversion.

An ASCII escape sequence for a Unicode character is \u followed by four ASCII hexadecimal digits representing the Unicode code point for the character.

For example, the Unicode euro character €, which is named EURO SIGN, has code point 20AC (using hexadecimal digits), so it can be represented using the ASCII escape sequence \u20AC.

You can use explicit ASCII escaping on input data. Because JSON data uses Unicode internally, when it is output, character-set conversion still applies, by default. However, for Oracle SQL functions json_value, json_query, and json_table you can use keyword ASCII to specify the automatic use of ASCII escape sequences for non-ASCII Unicode characters.

Oracle JSON Path Expression Syntax

Oracle JSON path expression syntax is an extension of JSON syntax. In particular, path expressions can use wildcards and array ranges.

You pass an Oracle JSON path expression and some JSON data to an Oracle SQL function or condition. The path expression is matched against the data, and the matching data is processed by the particular SQL function or condition. You can think of this matching process in terms of the path expression returning the matched data to the function or condition.

Clauses Used in Oracle SQL Functions and Conditions for JSON

This section describes the RETURNING, wrapper, and error handling clauses used in one or more of the Oracle SQL functions and conditions json_value, json_query, json_table, is json, is not json, and json_exists.

Oracle SQL Conditions IS JSON and IS NOT JSON

Oracle SQL conditions is json and is not json are complementary. You can use them in a CASE expression or the WHERE clause of a SELECT statement.

They test whether their argument is syntactically correct, that is, well-formed, as JSON data. If so, then is json returns true and is not json returns false. If the argument cannot be evaluated for some reason (for example, if an error occurs during parsing) then the data is simply considered to not be well-formed: is json returns false; is not json returns true. Well-formedness implies that the data is syntactically correct. (JSON data can be well formed in two senses, which we refer to as strict and lax syntax.)

CREATE TABLE j_purchaseorder
   (id          RAW (16) NOT NULL,
    date_loaded TIMESTAMP (6) WITH TIME ZONE,
    po_document CLOB
    CONSTRAINT ensure_json CHECK (po_document IS JSON));

INSERT INTO j_purchaseorder
  VALUES (
    SYS_GUID(),
    SYSTIMESTAMP,
    '{"PONumber"             : 1600,
      "Reference"            : "ABULL-20140421",
      "Requestor"            : "Alexis Bull",
      "User"                 : "ABULL",
      "CostCenter"           : "A50",
      "ShippingInstructions" : {"name"   : "Alexis Bull",
                                "Address": {"street"  : "200 Sporting Green",
                                            "city"    : "South San Francisco",
                                            "state"   : "CA",
                                            "zipCode" : 99236,
                                            "country" : "United States of America"},
                                "Phone" : [{"type" : "Office", "number" : "909-555-7307"},
                                           {"type" : "Mobile", "number" : "415-555-1234"}]},
      "Special Instructions" : null,
      "AllowPartialShipment" : true,
      "LineItems"            : [{"ItemNumber" : 1,
                                 "Part"       : {"Description" : "One Magic Christmas",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 13131092899},
                                 "Quantity"   : 9.0},
                                {"ItemNumber" : 2,
                                 "Part"       : {"Description" : "Lethal Weapon",
                                                 "UnitPrice"   : 19.95,
                                                 "UPCCode"     : 85391628927},
                                 "Quantity"   : 5.0}]}');

CREATE TABLE j_purchaseorder
   (id          RAW (16) NOT NULL,
    date_loaded TIMESTAMP WITH TIME ZONE,
    po_document CLOB
    CONSTRAINT ensure_json CHECK (po_document IS JSON (STRICT)));

Oracle SQL Condition JSON_EXISTS

Oracle SQL condition json_exists lets you use an Oracle JSON path expression as a row filter, to select rows based on the content of JSON documents.

You can use condition json_exists in a CASE expression or the WHERE clause of a SELECT statement. It checks for the existence of a particular value within JSON data: it returns true if the value is present and false if it is absent.

More precisely, json_exists returns true if the data it targets matches one or more JSON values. If no JSON values are matched then it returns false.

You can also use json_exists to create bitmap indexes for use with JSON data — see Example 39-20.

Error handlers ERROR ON ERROR, FALSE ON ERROR, and TRUE ON ERROR apply. The default is FALSE ON ERROR. The handler takes effect when any error occurs, but typically an error occurs when the given JSON data is not well-formed (using lax syntax). Unlike the case for conditions is json and is not json, condition json_exists expects the data it examines to be well-formed JSON data.

The second argument to json_exists is an Oracle JSON path expression followed by an optional RETURNING clause and an optional error clause. The path expression must target a single scalar value, or else a compile-time error is raised.

SELECT select_list
  FROM table WHERE json_exists(column, json_path error_handler ON ERROR);
       
SELECT select_list
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" NUMBER EXISTS PATH json_path)) AS "JT"
  WHERE jt.column_alias = 1;

Oracle SQL Function JSON_VALUE

Oracle SQL function json_value selects a scalar value from JSON data and returns it as a SQL value. You can also use json_value to create function-based B-tree indexes for use with JSON data — see "Indexes for JSON Data".

Function json_value has two required arguments and accepts optional returning and error clauses.

The first argument to json_value is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. 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 SQL expression is used as the context item for evaluating the path expression.

The second argument to json_value is an Oracle JSON path expression followed by an optional RETURNING clause and an optional error clause. The path expression must target a single scalar value, or else an error occurs.

Note that the default error-handling behavior is NULL ON ERROR, which means that no value is returned if an error occurs — an error is not raised. In particular, if the path expression targets a non-scalar value, such as an array, no error is raised, by default. To ensure that an error is raised, use ERROR ON ERROR.

SELECT json_value(po_document, '$.AllowPartialShipment')
 FROM j_purchaseorder;

SELECT json_value(po_document, '$.AllowPartialShipment' RETURNING NUMBER)
  FROM j_purchaseorder;

SELECT json_value(column, json_path RETURNING data_type error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";

Oracle SQL Function JSON_QUERY

Oracle SQL function json_query selects one or more values from JSON data and returns a string (VARCHAR2) that represents the JSON values. (Unlike function json_value, the return data type cannot be NUMBER). You can thus use json_query to retrieve fragments of a JSON document.

The first argument to json_query is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. 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 SQL expression is used as the context item for evaluating the path expression.

The second argument to json_query is an Oracle JSON path expression followed by an optional RETURNING clause, an optional wrapper clause, and an optional error clause. The path expression can target any number of JSON values.

In the RETURNING clause you can specify only data type VARCHAR2; you cannot specify NUMBER.

The wrapper clause determines the form of the returned string value.

Note that the error clause for json_query can specify EMPTY ON ERROR, which means that an empty array ([]) is returned in case of error (no error is raised).

Example 39-9 shows an example of the use of Oracle SQL function json_query with an array wrapper. For each document it returns a VARCHAR2 value whose contents represent a JSON array with elements the phone types, in an unspecified order. For the document in Example 39-4 the phone types are "Office" and "Mobile", and the array returned is either ["Mobile", "Office"] or ["Office", "Mobile"].

SELECT json_query(po_document, '$.ShippingInstructions.Phone[*].type'
                               WITH WRAPPER)
  FROM j_purchaseorder;

Note that if path expression $.ShippingInstructions.Phone.type were used in Example 39-9 it would give the same result. Because of Oracle JSON path-expression syntax relaxation, [*].type is equivalent to .type.

SELECT json_query(column, json_path
                  RETURNING data_type array_wrapper error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type FORMAT JSON array_wrapper
                  PATH json_path)) AS "JT";

Oracle SQL Function JSON_TABLE

Oracle SQL function json_table projects JSON data into a relational format. You use json_table to decompose the result of JSON expression evaluation into the relational rows and columns of a new, virtual table, which you can also think of as an inline relational 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.

In particular, a common use of json_table is to create a relational view of JSON data. You can use such a view just as you would use any relational table or view. This lets applications, tools, and programmers operate on JSON data as if it were relational, that is, without consideration of the syntax of JSON or JSON path expressions.

Defining a relational 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 relational 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. Example 39-17 illustrates the creation of a relational view using json_table.

You use json_table in a SQL FROM clause. It is thus a row source: it generates a row of relational 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.

The first argument to json_table is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. 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 SQL expression is used as the context item for evaluating the row path expression.

The second argument to json_table is an Oracle 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 relational columns, which you specify in the COLUMNS clause. Each of those rows is matched against zero or more column path expressions to generate the relational 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 or NUMBER (the same as for the RETURNING clause of other Oracle SQL functions for JSON), followed by an optional value clause and a mandatory PATH clause. The default data type is VARCHAR2(4000).

• A nested columns specification consists of the keyword NESTED followed by an optional PATH keyword, an Oracle 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 39-16 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.

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))
        = 0;

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 = 0 AND has_zip = 'true';

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

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

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

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

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;

How To Tell Whether a Function-Based Index for JSON Data Is Picked Up

To determine whether a given query picks up a given function-based index, look for the index name in the execution plan for the query. For example, given the index defined in Example 39-22, an execution plan for the json_value query of Example 39-18 references an index scan with index po_num_id1.

Creating Bitmap Indexes for Oracle SQL Condition JSON_EXISTS

Example 39-20 creates a bitmap index for the value returned by json_exists. This is the right kind of index to use because there are only two possible return values for a condition (true and false).

CREATE BITMAP INDEX has_zipcode_idx
  ON j_purchaseorder (json_exists(po_document, 
                                  '$.ShippingInstructions.Address.zipCode'));

Example 39-21 creates a bitmap index for a value returned by json_value. This is an appropriate index to use if there are only few possible values for property CostCenter in your data.

CREATE BITMAP INDEX cost_ctr_idx
  ON j_purchaseorder (json_value(po_document, '$.CostCenter'));

Creating JSON_VALUE Function-Based Indexes

Example 39-22 creates a function-based index for SQL function json_value on property PONumber of the object that is in column po_document of table j_purchaseorder. The object is passed as the path-expression context item.

CREATE UNIQUE INDEX po_num_idx1
  ON j_purchaseorder (json_value(po_document, '$.PONumber' 
                                 RETURNING NUMBER ERROR ON ERROR));

The use of ERROR ON ERROR here means that if the data contains a record that either has no PONumber property or has a PONumber property with a non-number value then index creation fails. And if the index exists then trying to insert such a record fails.

An alternative is to create an index using the simplified syntax described in "Simple Dot-Notation Access to JSON Data". Example 39-23 illustrates this; it indexes both scalar and non-scalar results, corresponding to what a dot-notation query can return.

CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder po (po.po_document.PONumber);

The indexes created in both Example 39-22 and Example 39-23 can be picked up for either a query that uses dot-notation syntax or a query that uses json_value.

If the index of Example 39-23 is picked up for a json_value query then filtering is applied after index pickup, to test for the correct property value. Non-scalar values can be stored in this index, since dot-notation queries can return such values, but a json_value query cannot, so such values are filtered out after index pickup.

Oracle recommends that you create a function-based index for json_value using one of these forms:

• A json_value expression that specifies a RETURNING data type and uses ERROR ON ERROR.

  The indexed values are only (non-null) scalar values of the specified data type. The index can nevertheless be used in dot-notation queries that lead to such a scalar result.

• Dot-notation syntax

  The indexed values correspond to the flexible behavior of dot-notation queries, which return JSON values whenever possible. They can include non-scalar JSON values (JSON objects and arrays). They can match dot-notation queries in addition to json_value queries. The index is used to come up with an initial set of matches, which are then filtered according to the specifics of the query. For example, any indexed values that are not JSON scalars are filtered out.

Indexes created in both of these ways can thus be used with both dot-notation queries and json_value queries.

Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries

An index created using json_value with ERROR ON ERROR can be used for a query involving json_table, if the WHERE clause refers to a column projected by json_table and the effective JSON path that targets that column matches the indexed path expression. The index acts as a constraint on the indexed path, to ensure that only one (non-null) scalar JSON value is projected for each item in the JSON collection.

The query in Example 39-24 thus makes use of the index created in Example 39-22.

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.po_document, '$'
         COLUMNS po_number  NUMBER(5) PATH '$.PONumber',
                 reference  VARCHAR2(30 CHAR) PATH '$.Reference',
                 requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
                 userid     VARCHAR2(10 CHAR) PATH '$.User',
                 costcenter VARCHAR2(16 CHAR) PATH '$.CostCenter') jt
  WHERE po_number = 1600;

Data Type Considerations for JSON_VALUE Indexing and Querying

By default, the Oracle SQL functions for JSON return a VARCHAR2 value. When you create a function-based index using json_value, unless you use a RETURNING clause to specify a different return data type, the index is not picked up for a query that expects a non-VARCHAR2 value.

For example, in the query of Example 39-25, json_value uses RETURNING NUMBER. The index created in Example 39-22 can be picked up for this query, because the indexed json_value expression specifies a return type of NUMBER.

SELECT count(*) FROM j_purchaseorder po  WHERE json_value(po_document, '$.PONumber' RETURNING NUMBER) > 1500;

But the index created in Example 39-23 does not use RETURNING NUMBER (the return type is VARCHAR2(4000), by default), so it cannot be picked up for a such a query.

Now consider the queries in Example 39-26 and Example 39-27, which use json_value without a RETURNING clause, so that the value returned is of type VARCHAR2.

SELECT count(*) FROM j_purchaseorder po  WHERE to_number(json_value(po_document, '$.PONumber')) > 1500;

SELECT count(*) FROM j_purchaseorder po  WHERE json_value(po_document, '$.PONumber') > 1500;

In Example 39-26, SQL function to_number explicitly converts the VARCHAR2 value returned by json_value to a number. Similarly, in Example 39-27, comparison condition > (greater-than) implicitly converts the value to a number.

Neither of the indexes of Example 39-22 and Example 39-23 is picked up for either of these queries. The queries might return the right results in each case, because of type-casting, but the indexes cannot be used to evaluate the queries.

Consider also what happens if some of the data cannot be converted to a particular data type. For example, given the queries in Example 39-25, Example 39-26, and Example 39-27, what happens to a PONumber value such as "alpha"?

For Example 39-26 and Example 39-27, the query stops in error because of the attempt to cast the value to a number. For Example 39-25, however, because the default error handling behavior is NULL ON ERROR, the non-number value "alpha" is simply filtered out. The value is indexed, but it is ignored for the query.

Similarly, if the query used, say, DEFAULT '1000' ON ERROR, that is, if it specified a numeric default value, then no error would be raised for the value "alpha": the default value of 1000 would be used.

Indexing Multiple JSON Properties Using a Composite B-Tree Index

To index multiple properties of a JSON object you first create virtual columns for them. Then you create a composite B-tree index on the virtual columns. Example 39-28 and Example 39-29 illustrate this. Example 39-28 creates virtual columns userid and costcenter for JSON object properties User and CostCenter, respectively.

ALTER TABLE j_purchaseorder ADD (userid VARCHAR2(20)
  GENERATED ALWAYS AS (json_value(po_document, '$.User' RETURNING VARCHAR2(20))));

ALTER TABLE j_purchaseorder ADD (costcenter VARCHAR2(6)
  GENERATED ALWAYS AS (json_value(po_document, '$.CostCenter'
                       RETURNING VARCHAR2(6))));

Example 39-29 creates a composite B-tree index on the virtual columns of Example 39-28.

CREATE INDEX user_cost_ctr_idx on j_purchaseorder(userid, costcenter);

A SQL query that references either the virtual columns or the corresponding JSON data (object properties) picks up the composite index. This is the case for both of the queries in Example 39-30.

SELECT po_document FROM j_purchaseorder WHERE userid      = 'ABULL'
                                          AND costcenter  = 'A50';

SELECT po_document
  FROM j_purchaseorder WHERE json_value(po_document, '$.User')       = 'ABULL'
                         AND json_value(po_document, '$.CostCenter') = 'A50';

These two queries have the same effect, including the same performance. However, the first query form does not target the JSON data itself; it targets the virtual columns that are used to index that data.

The data does not depend logically on any indexes implemented to improve query performance. If you want this independence from implementation to be reflected in your code, then use the second query form. Doing that ensures that the query behaves the same functionally with or without the index — the index serves only to improve performance.