For example, given the index defined in Example 27-4, an execution plan for the json_value query of Example 13-1 references an index scan with index po_num_id1.

This is illustrated by Example 27-1.

Example 27-2 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 field CostCenter in your data.

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

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

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

The use of ERROR ON ERROR here means that if the data contains a record that has no PONumber field, has more than one PONumber field, or has a PONumber field 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 27-3 illustrates this; it indexes both scalar and non-scalar results, corresponding to what a dot-notation query can return.

The indexes created in both Example 27-4 and Example 27-3 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 27-3 is picked up for a json_value query then filtering is applied after index pickup, to test for the correct field 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.

If you want to allow indexing of data that might be missing the field targeted by a json_value expression, then use a NULL ON EMPTY clause, together with an ERROR ON ERROR clause. Example 27-5 illustrates this.

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

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

• A json_value expression that specifies a RETURNING data type, uses ERROR ON ERROR (and optionally uses NULL ON EMPTY).

  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.

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

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

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

CREATE UNIQUE INDEX po_ref_idx1
  ON j_purchaseorder (json_value(po_document, '$.Reference'
                                 RETURNING VARCHAR2(200) ERROR ON ERROR
                                 NULL ON EMPTY));

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 27-6 thus makes use of the index created in Example 27-4.

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;

Example 27-14 and Example 27-15 illustrate this. Example 27-14 creates virtual columns userid and costcenter for JSON object fields User and CostCenter, respectively.

Example 27-15 creates a composite B-tree index on the virtual columns of Example 27-14.

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

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.

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

CREATE INDEX user_cost_ctr_idx on j_purchaseorder(userid, costcenter);

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

Introduction to JSON Search Indexes

You create a JSON search index using CREATE SEARCH INDEX with the keywords FOR JSON. Example 27-17 illustrates this.

The column on which you create a JSON search index can be of data type VARCHAR2, CLOB, or BLOB. It must be known to contain only well-formed JSON data, which means that it has an is json check constraint. CREATE SEARCH INDEX raises an error if the column is not known to contain JSON data.

If the name of your JSON search index is present in the execution plan for your query, then you know that the index was in fact picked up for that query. You will see a line similar to that shown in Example 27-19.

You can specify a PARAMETERS clause to override the default settings of certain configurable options. By default (no PARAMETERS clause), the index is synchronized on commit and both text and numeric ranges are indexed.

If your queries that make use of a JSON search index involve only full-text search or string-equality search, and never involve string-range search or numeric or temporal search (equality or range), then you can save some index maintenance time and some disk space by specifying TEXT for parameter SEARCH_ON. The default value of SEARCH_ON is TEXT_VALUE, which means index numeric ranges as well as text.

Also by default, the search index created records and maintains persistent data-guide information, which requires some maintenance overhead. You can inhibit this support for persistent data-guide information by specifying DATAGUIDE OFF in the PARAMETERS clause.

A JSON search index is maintained asynchronously. Until it is synchronized, the index is not used for data that has been modified or newly inserted. An index can improve query performance, but the act of synchronizing it with the data affects performance negatively while it occurs. In particular, it can negatively affect DML operations.

There are essentially three ways to synchronize a JSON search index. Each is typically appropriate for a different use case.

• Synchronize on commit.

  This is appropriate when commits are infrequent and it is important that the committed changes be immediately visible to other operations (such as queries). (A stale index can result in uncommitted changes not being visible.) Example 27-17 creates a search index that is synchronized on commit.

• Synchronize periodically at some interval of time.

  For online transaction-processing (OLTP) applications, which require fast and reliable transaction handling with high throughput, and which typically commit each operation, periodic index synchronization is often appropriate. In this case, the synchronization interval is generally greater than the time between commits, and it is not essential that the result of each commit be immediately visible to other operations.Example 27-18 creates a search index that is synchronized each second.

• Synchronize on demand, for example at a time when database load is reduced.

  You generally do this infrequently — the index is synchronized less often than with on-commit or interval synchronizing. This method is typically appropriate when DML performance is particularly important.

If you need to invoke procedures in package CTX_DDL, such as CTX_DDL.sync_index to manually sync the index, then you need privilege CTXAPP.

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document) FOR JSON;

CREATE SEARCH INDEX po_search_1_sec_idx ON j_purchaseorder (po_document)
  FOR JSON
  PARAMETERS('SYNC (EVERY "FREQ=SECONDLY; INTERVAL=1")

|* 2|   DOMAIN INDEX     | PO_SEARCH_IDX |     |     |     4 (0)

Full-Text Search of JSON Data

You can use SQL/JSON condition json_textcontains in a CASE expression or the WHERE clause of a SELECT statement to perform a full-text search of JSON data that is stored in a VARCHAR2, BLOB, or CLOB column.

Oracle Text technology underlies SQL condition json_textcontains. This means, for instance, that you can query for text that is near some other text, or query use fuzzy pattern-matching.

To be able to use condition json_textcontains, you first must create a JSON search index. If you do not, then an error is raised when json_textcontains is used.

Example 27-20 shows a full-text query that finds purchase-order documents that contain the keyword Magic in any of the line-item part descriptions.

SELECT po_document FROM j_purchaseorder
  WHERE json_textcontains(po_document, '$.LineItems.Part.Description', 'Magic');

Ad Hoc Queries of JSON Data

Example 27-21 shows some non full-text queries of JSON data that also make use of the JSON search index created in Example 27-17.

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document, '$.ShippingInstructions.Address.country');

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document, '$?(@.User == "AKHOO"
                                     && @.LineItems.Quantity > 8)');

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