28 Indexes for JSON Data

You can index scalar values in your JSON data using function-based indexes. In addition, you can define a JSON search index, which is useful for both ad hoc structural queries and full-text queries.

• Overview of Indexing JSON Data
  You can index particular scalar values within your JSON data using function-based indexes. You can index JSON data in a general way using a JSON search index, for ad hoc structural queries and full-text queries.
• How To Tell Whether a Function-Based Index for JSON Data Is Picked Up
  Whether or not a particular index is picked up for a given query is determined by the optimizer. 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.
• Creating Bitmap Indexes for JSON_VALUE
  You can create a bitmap index for SQL/JSON function json_value. A bitmap index can be appropriate whenever your queries target only a small set of JSON values.
• Creating B-Tree Indexes for JSON_VALUE
  You can create a B-tree function-based index for SQL/JSON function json_value. You can use the standard syntax for this, explicitly specifying json_value, or you can use dot-notation syntax with an item method. Indexes created in either of these ways can 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. In this case 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.
• Using a JSON_VALUE Function-Based Index with JSON_EXISTS Queries
  An index created using SQL/JSON function json_value with ERROR ON ERROR can be used for a query involving SQL/JSON condition json_exists.
• Data Type Considerations for JSON_VALUE Indexing and Querying
  For a function-based index created using SQL/JSON function json_value to be picked up for a given query, the data type returned by json_value in the query must match the type specified in the index.
• Creating Multivalue Function-Based Indexes for JSON_EXISTS
  For JSON data that is stored as JSON type you can use a multivalue function-based index for SQL/JSON condition json_exists. Such an index targets scalar JSON values, either individually or as elements of a JSON array.
• Using a Multivalue Function-Based Index
  A json_exists query in a WHERE clause can pick up a multivalue function-based index only if the data it targets matches the scalar type specified in the index.
• Indexing Multiple JSON Fields Using a Composite B-Tree Index
  To index multiple fields of a JSON object you can create a composite B-tree index using multiple path expressions with SQL/JSON function json_value or dot-notation syntax.
• JSON Search Index for Ad Hoc Queries and Full-Text Search
  A JSON search index is a general index. It can improve the performance of both (1) ad hoc structural queries, that is, queries that you might not anticipate or use regularly, and (2) full-text search. It is an Oracle Text index that is designed specifically for use with JSON data.

28.1 Overview of Indexing JSON Data

You can index particular scalar values within your JSON data using function-based indexes. You can index JSON data in a general way using a JSON search index, for ad hoc structural queries and full-text queries.

As always, function-based indexing is appropriate for queries that target particular functions, which in the context of SQL/JSON functions means particular SQL/JSON path expressions. This indexing is not very helpful for queries that are ad hoc, that is, arbitrary. Define a function-based index if you know that you will often query a particular path expression.

Regardless of the SQL data type you use to store JSON data, you can use a B-tree or bitmap function-based index for SQL/JSON function json_value queries. Such an index targets a single scalar JSON value. A bitmap index can be appropriate wherever the number of possible values for the function is small. For example, you can use a bitmap index for json_value if the values targeted are expected to be few.

For JSON data that is stored as JSON type you can use a multivalue function-based index for SQL/JSON condition json_exists. Such an index targets scalar JSON values, either individually or (especially) as elements of a JSON array.

Although a multivalue index can index a single scalar value, if you expect a path expression to target such a value then it is more performant to use a B-tree or bitmap index. Use a multivalue index especially to index a path expression that you expect to target an array of scalar values.

SQL/JSON path expressions that contain predicates can be used in queries that pick up a function-based index. But a path expression that you use to define a function-based index cannot contain predicates.

If you query in an ad hoc manner then define a JSON search index. This is a general index, not targeted to any specific path expression. It is appropriate for structural queries, such as looking for a JSON field with a particular value, and for full-text queries using Oracle SQL condition json_textcontains, such as looking for a particular word among various string values.

You can of course define both function-based indexes and a JSON search index for the same JSON column.

A JSON search index is an Oracle Text (full-text) index designed specifically for use with JSON data.

Note:

Oracle recommends that you use AL32UTF8 as the database character set. Automatic character-set conversion can take place when creating or applying an index. Such conversion can be lossy, which can mean that some data that you might expect to be returned by a query is not returned. See Character Sets and Character Encoding for JSON Data.

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

Whether or not a particular index is picked up for a given query is determined by the optimizer. 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 28-3, an execution plan for each of the queries in these examples references an index scan with index po_num_id1: Example 28-5, Example 28-6, Example 28-7, Example 28-8, and Example 28-10

• Given the index defined in Example 28-13, an execution plan for the queries in examples Example 28-14 and Example 28-15 references an index scan with index mvi_1.

When a multivalue index is picked up, the execution plan also shows (MULTI VALUE) for the index range scan, and the filter used in the plan is JSON_QUERY, not JSON_EXISTS2. If the execution plan does not use a multivalue index for a given json_exists query, then the filter is JSON_EXISTS2.

28.3 Creating Bitmap Indexes for JSON_VALUE

You can create a bitmap index for SQL/JSON function json_value. A bitmap index can be appropriate whenever your queries target only a small set of JSON values.

Example 28-1 Creating a Bitmap Index for JSON_VALUE

This is an appropriate index to create provided there are only a few possible values for field CostCenter in your data.

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

28.4 Creating B-Tree Indexes for JSON_VALUE

You can create a B-tree function-based index for SQL/JSON function json_value. You can use the standard syntax for this, explicitly specifying json_value, or you can use dot-notation syntax with an item method. Indexes created in either of these ways can be used with both dot-notation queries and json_value queries.

Example 28-3 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 dot-noation syntax described in Simple Dot-Notation Access to JSON Data, applying an item method to the targeted data. Example 28-2 illustrates this.

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

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 28-4 illustrates this.

Oracle recommends that you create a function-based index for json_value using one of the following forms. In each case the index can be used in both dot-notation and json_value queries that lead to a scalar result of the specified JSON data type.

• Dot-notation syntax, with an item method applied to the value to be indexed. The indexed values are only scalars of the data type specified by the item method.

• A json_value expression that specifies a RETURNING data type. It can optionally use ERROR ON ERROR and NULL ON EMPTY. The indexed values are only scalars of the data type specified by the RETURNING clause.

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

Example 28-2 Creating a Function-Based Index for a JSON Field: Dot Notation

Item method number() causes the index to be of numeric type. Always apply an item method to the targeted data when you use dot notation to create a function-based index.

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

Example 28-3 Creating a Function-Based Index for a JSON Field: JSON_VALUE

Item method number() causes the index to be of numeric type. Alternatively you can instead use clause RETURNING NUMBER.

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

Example 28-4 Specifying NULL ON EMPTY for a JSON_VALUE Function-Based Index

Clause RETURNING VARCHAR2(200) causes the index to be a SQL string of maximum length 200 characters. You could use item method string() in the path expression instead, but in that case the default return type of VARCHAR2(4000) is used.

Because of clause NULL ON EMPTY, index po_ref_idx1 can index JSON documents that have no Reference field.

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

28.5 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. In this case 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.

For the index to be used in this way each of these conditions must hold:

• The query WHERE clause refers to a column projected by json_table.

• The data type of that column matches the data type used in the index definition.

• The effective SQL/JSON path that targets that column matches the indexed path expression.

The query in Example 28-5 thus makes use of the index created in Example 28-3.

Note:

A function-based index created using a json_value expression or dot notation can be picked up for a corresponding occurrence in a query WHERE clause only if the occurrence is used in a SQL comparison condition, such as >=. In particular, it is not picked up for an occurrence used in condition IS NULL or IS NOT NULL.

See Oracle Database SQL Language Reference for information about SQL comparison conditions.

Example 28-5 Use of a JSON_VALUE Function-Based Index with a JSON_TABLE Query

The index can be picked up because the column SQL type, NUMBER(5), matches the type used in the index.

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;

28.6 Using a JSON_VALUE Function-Based Index with JSON_EXISTS Queries

An index created using SQL/JSON function json_value with ERROR ON ERROR can be used for a query involving SQL/JSON condition json_exists.

In order for a json_value function-based index to be picked up for one of the comparisons of the query, the type of that comparison must be the same as the returning SQL data type for the index. The SQL data types used are those mentioned for item methods double(), float(), number(), string(),timestamp(), date(), dateWithTime(), dsInterval(), and ymInterval() — see SQL/JSON Path Expression Item Methods.

For example, if the index returns a number then the comparison type must also be number. If the query filter expression contains more than one comparison that matches a json_value index, the optimizer chooses one of the indexes.

The type of a comparison is determined as follows:

1. If the SQL data types of the two comparison terms (sides of the comparison) are different then the type of the comparison is unknown, and the index is not picked up. Otherwise, the types are the same, and this type is the type of the comparison.

2. If a comparison term is of SQL data type string (a text literal) then the type of the comparison is the type of the other comparison term.

3. If a comparison term is a path expression with a function step whose item method imposes a SQL match type then that is also the type of that comparison term. The item methods that impose a SQL match type are double(), float(), number(), string(), timestamp(), date(), dateWithTime(), dsInterval(), and ymInterval().

4. If a comparison term is a path expression with no such function step then its type is SQL string (text literal).

Example 28-3 creates a function-based index for json_value on field PONumber. The index indexes NUMBER values.

Each of the queries Example 28-6, Example 28-7, and Example 28-8 can make use of this index when evaluating its json_exists condition. Each of these queries uses a comparison that involves a simple path expression that is relative to the absolute path expression $.PONumber. The relative simple path expression in each case targets the current filter item, @, but in the case of Example 28-8 it transforms (casts) the matching data to SQL data type NUMBER.

Example 28-6 JSON_EXISTS Query Targeting Field Compared to Literal Number

This query makes use of the index because:

1. One comparison term is a path expression with no function step, so its type is SQL string (text literal).

2. Because one comparison term is of type string, the comparison has the type of the other term, which is number (the other term is a numeral).

3. The type of the (lone) comparison is the same as the type returned by the index: number.

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@ > 1500)');

Example 28-7 JSON_EXISTS Query Targeting Field Compared to Variable Value

This query can make use of the index because:

1. One comparison term is a path expression with no function step, so its type is SQL string (text literal).

2. Because one comparison term is of type string, the comparison has the type of the other term, which is number (the other term is a variable that is bound to a number).

3. The type of the (lone) comparison is the same as the type returned by the index: number.

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@ > $d)'
                    PASSING 1500 AS "d");

Example 28-8 JSON_EXISTS Query Targeting Field Cast to Number Compared to Variable Value

This query can make use of the index because:

1. One comparison term is a path expression with a function step whose item method (number()) transforms the matching data to a number, so the type of that comparison term is SQL number.

2. The other comparison term is a numeral, which has SQL type number. The types of the comparison terms match, so the comparison has this same type, number.

3. The type of the (lone) comparison is the same as the type returned by the index: number.

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@.number() > $d)'
                    PASSING 1500 AS "d");

Example 28-9 JSON_EXISTS Query Targeting a Conjunction of Field Comparisons

Just as for Example 28-6, this query can make use of the index on field PONumber. If a json_value index is also defined for field Reference then the optimizer chooses which index to use for this query.

SELECT count(*) FROM j_purchaseorder
  WHERE json_exists(po_document,
                    '$?(@.PONumber > 1500
                        && @.Reference == "ABULL-20140421")');

28.7 Data Type Considerations for JSON_VALUE Indexing and Querying

For a function-based index created using SQL/JSON function json_value to be picked up for a given query, the data type returned by json_value in the query must match the type specified in the index.

When RETURNING DATE is used with json_value, the same time-handling behavior (truncation or preservation) must be used in both the index and the query, for the index to be picked up. That is, either RETURNING DATE PRESERVE TIME must be used in both, or RETURNING DATE TRUNCATE TIME (or RETURNING DATE, since truncation is the default behavior) must be used in both.

By default, SQL/JSON function json_value returns a VARCHAR2 value. When you create a function-based index using json_value, unless you use a RETURNING clause or an item method 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 28-10, json_value uses RETURNING NUMBER. The index created in Example 28-3 can be picked up for this query, because the indexed json_value expression specifies a return type of NUMBER. Without keywords RETURNING NUMBER in the index the return type it specifies would be VARCHAR2(4000) (the default) — the index would not be picked up for such a query.

Similarly, the index created in Example 28-2 can be picked up for the query because it uses item method number(), which also imposes a return type of NUMBER.

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

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

Neither of the indexes of Example 28-3 and Example 28-2 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 28-10, Example 28-11, and Example 28-12, what happens to a PONumber value such as "alpha"?

For Example 28-11 and Example 28-12, the query stops in error because of the attempt to cast the value to a number. For Example 28-10, 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.

Note:

For a function-based index based on SQL/JSON function json_value to be picked up for a given query, the same return data type and handling (error, empty, and mismatch) must be used in both the index and the query.

This means that if you change the return type or handling in a query, so that it no longer matches what is specified in the index, then you must rebuild any persistent objects that depend on that query pattern. (The same applies to materialized views, partitions, check constraints and PL/SQL subprograms that depend on that pattern.)

Example 28-10 JSON_VALUE Query with Explicit RETURNING NUMBER

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

Example 28-11 JSON_VALUE Query with Explicit Numerical Conversion

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

Example 28-12 JSON_VALUE Query with Implicit Numerical Conversion

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

28.8 Creating Multivalue Function-Based Indexes for JSON_EXISTS

For JSON data that is stored as JSON type you can use a multivalue function-based index for SQL/JSON condition json_exists. Such an index targets scalar JSON values, either individually or as elements of a JSON array.

The main use of a multivalue index is to index scalar array elements. A multivalue index can also index a single scalar value, but for queries that target a single value it is more performant to use a B-tree or bitmap index.

In a query, you use json_exists in the WHERE clause of a SELECT statement. Condition json_exists returns true if the data it targets matches the SQL/JSON path expression (or equivalent simple dot-notation syntax) in the query. Otherwise it returns false. It is common for the path expression to include a predicate — matching requires that the targeted data satisfy the predicate.

You create a multivalue index using CREATE INDEX with keyword MULTIVALUE, and using the same simple dot-notation syntax that you use in queries to specify the path to the indexed data.

In addition, for the index to be picked up by a query, the index path expression must include a data-type conversion item method (other than binary() and dateWithTime()), to indicate the SQL data type of the data to be indexed. See SQL/JSON Path Expression Item Methods for information about the data-type conversion item methods.

If the index uses an item method with "only" in its name then only queries that also use that same item method can pick up the index. Otherwise (with a non-"only" method), any query that targets a scalar value (possibly as an array element) that can be converted to the type indicated by the item method can pick up the index.

For example, a multivalue index that uses item method numberOnly() can only be picked up for a query that also uses numberOnly(). But an index that uses number() can be picked up for a query that matches any scalar (such as the string "3.14") that can be converted to a number.

You can create more than one multivalue index for a given target. For example, you can create one index for a field month that uses number() and another for the same field that uses string().

Example 28-13 Creating a Multivalue Index for JSON_EXISTS

The multivalue index created here indexes the values of top-level field credit_score. The table alias (t in this case) is required.

If the credit_score value targeted by a query is an array then the index can be picked up for any array elements that are numbers. If the value is a scalar then the index can be picked up if the scalar is a number.

CREATE MULTIVALUE INDEX mvi_1 ON mytable t
  (t.jcol.credit_score.numberOnly());

28.9 Using a Multivalue Function-Based Index

A json_exists query in a WHERE clause can pick up a multivalue function-based index only if the data it targets matches the scalar type specified in the index.

A multivalue index defined using a data-type conversion item method (such as numberOnly()) with "only" in its name can be picked up only by json_exist queries that also use that same item method. That is, the query must use the same item method explicitly.

A multivalue index defined using a data-type conversion item method (such as number()) without "only" in its name can be picked up by any query that targets a scalar value (possibly as an array element) that can be converted to the type indicated by the item method can pick up the index. See SQL/JSON Path Expression Item Methods for information about the data-type conversion item methods.

The examples here use SQL/JSON condition json_exists in a WHERE clause to check for a credit_score value of 750. They are discussed in terms of whether they can pick up multivalue index mvi_1, which is defined in Example 28-13 using item method numberOnly().

If index mvi_1 had instead been defined used item method number(), then all of the queries here could pick up the index for a numeric credit_score value of 750, a string credit_score value of "750", or an array credit_score value with numeric elements of 750 or string elements of "750".

Example 28-14 WHERE Clause Condition With Item Method numberOnly()

This example uses item method numberOnly() in a WHERE clause. The query can pick up index mvi_1 when the path expression targets either a numeric credit_score of 750 (e.g. credit_score : 750) or an array credit_score with one or more numeric elements of 750 (e.g. credit_score : [630, 750, 702, 690, 750]). It cannot pick up index mvi_1 for targeted string values of "750" (e.g. credit_score : "750" or credit_score : [630, "750"]).

SELECT count(*) FROM mytable
  WHERE json_exists(jcol, '$.credit_score?(@.numberOnly() == 750)');

Example 28-15 WHERE Clause Condition Without Item Method numberOnly()

These two queries do not use item method numberOnly(). The first uses method number(), which converts the targeted data to a number, if possible. The second does no type conversion of the targeted data.

Index mvi_1 cannot be picked up by either of these queries, even if the targeted data is the number 750. For the index to be picked up, a query must use numberOnly(), because the index is defined using numberOnly().

SELECT count(*) FROM mytable t
  WHERE json_exists(jcol, '$.credit_score?(@.number() == 750)');

SELECT count(*) FROM mytable t
  WHERE json_exists(jcol, '$.credit_score?(@ == 750)');

28.10 Indexing Multiple JSON Fields Using a Composite B-Tree Index

To index multiple fields of a JSON object you can create a composite B-tree index using multiple path expressions with SQL/JSON function json_value or dot-notation syntax.

Example 28-16 illustrates this. A SQL query that references the corresponding JSON data (object fields) picks up the composite index. Example 28-17 illustrates this.

Alternatively, you can create virtual columns for the JSON object fields you want to index, and then create a composite B-tree index on those virtual columns. In that case a SQL query that references either the virtual columns or the corresponding JSON data (object fields) picks up the composite index. The query performance is the same in both cases.

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

Example 28-16 Creating a Composite B-tree Index For JSON Object Fields

CREATE INDEX user_cost_ctr_idx ON
  j_purchaseorder(json_value(po_document, '$.User'
                             RETURNING VARCHAR2(20),
                  json_value(po_document, '$.CostCenter'
                             RETURNING VARCHAR2(6)));

Example 28-17 Querying JSON Data Indexed With a Composite B-tree Index

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

28.11 JSON Search Index for Ad Hoc Queries and Full-Text Search

A JSON search index is a general index. It can improve the performance of both (1) ad hoc structural queries, that is, queries that you might not anticipate or use regularly, and (2) full-text search. It is an Oracle Text index that is designed specifically for use with JSON data.

Note:

For JSON data stored as JSON type, an alternative to creating and maintaining a JSON search index is to populate the JSON column into the In-Memory Column Store (IM column store).

For support for ad hoc queries, place the column or its table in the IM column store without using keyword TEXT. For support for full-text search, place the column in the IM column store using keyword TEXT. You can do both, for both kinds of query support. (JSON type is available only if database initialization parameter compatible is at least 20.)

If you both place a JSON type column in the IM column store and define a JSON search index on it then only the search index is used, for both ad hoc and full-text queries. A search index always takes precedence over the use of the IM column store.

Note:

If you have created a JSON search index then you can also use PL/SQL procedure CTX_QUERY.result_set to perform another kind of full-text search of the indexed JSON data: facet search.

CTX_QUERY search is optimized to produce various kinds of search hits all at once, rather than, for example, using multiple separate queries with SQL function contains.

To search using one of these procedures you pass it a result set descriptor (RSD), which specifies (as a JSON object with predefined operator fields $query, $search, and $facet) the JSON values you want to find from your indexed JSON data, and how you want them grouped or aggregated. The values you can retrieve and act on are either JSON scalars or JSON arrays of scalars.

(Operator-field $query is also used in SODA query-by-example (QBE) queries. You can use operator $contains in the value of field $query for full-text matching similar to that provided by Oracle SQL condition json_textcontains.)

The RSD fields serve as an ordered template, specifying what to include in the output result set. (In addition to the found JSON data, a result set typically includes a list of search-hit rowids and some counts.)

A $facet field value is a JSON array of facet objects, each of which defines JSON data located at a particular path and perhaps satisfying some conditions, and perhaps an aggregation operation to apply to that data.

You can aggregate facet data using operators $count, $min, $max, $avg, and $sum. For example, $sum returns the sum of the targeted data values. You can apply an aggregation operator to all scalar values targeted by a path, or you can apply it separately to buckets of such values, defined by different ranges of values.

Finally, you can obtain the counts of occurrences of distinct values at a given path, using operator $uniqueCount.

For example, consider this $facet value:

[{"$uniqueCount" : "zebra.name"},
 {"$sum"         : {"path"  : "zebra.price",                   
                    "bucket : [{"$lt"  : 3000},
                               {"$gte" : 3000}]},
 {"$avg"         : "zebra.rating"}]

When query results are returned, the value of field $facet in the output is an array of three objects, with these fields:

• zebra.name — The number of occurrences of each zebra name.

• zebra.price — The sum of zebra prices, in two buckets: prices less than 3000 and prices at least 3000.

• zebra.rating — The average of all zebra ratings. (Zebras with no rating are ignored.)

[{"zebra.name"   : [{"value":"Zigs",
                     "$uniqueCount:2},                  
                    {"value":"Zigzag",
                     "$uniqueCount:1},             
                    {"value":"Storm",
                     "$uniqueCount:1}]},
 {"zebra.price"  : [{"value":1000,
                     "$uniqueCount:2},                  
                    {"value":3000,
                     "$uniqueCount:2},                  
                    {"value":2000,
                     "$uniqueCount:1}]},
 {"zebra.rating" : {"$avg":4.66666666666666666667}}]

Note:

If you created a JSON search index using Oracle Database 12c Release 1 (12.1.0.2) then Oracle recommends that you drop that index and create a new search index for use with later releases, using CREATE SEARCH INDEX as shown here.

Note:

You must rebuild any JSON search indexes and Oracle Text indexes created prior to Oracle Database 18c if they index JSON data that contains object fields with names longer than 64 bytes. Otherwise, such fields might not be searchable until they are reindexed. See Oracle Database Upgrade Guide for more information.

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

The column on which you create a JSON search index can be of data type JSON, VARCHAR2, CLOB, or BLOB. It must be known to contain only well-formed JSON data, which means that it is either of type JSON or 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 28-20.

You can specify a PARAMETERS clause when creating a search index, 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.

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 28-18 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 28-19 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.

To create a JSON search index you need privilege CTXAPP. To create the index with a synchronization interval, as opposed to having the index be synchronized on commit, you also need privilege CREATE JOB.

If your queries that make use of a JSON search index never involve numeric ranges 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.

Note:

To alter a JSON search index j_s_idx, you use ALTER INDEX j_s_idx REBUILD ... (not ALTER SEARCH INDEX j_s_idx ...).

Example 28-18 Creating a JSON Search Index That Is Synchronized On Commit

Synchronization on commit is the default behavior, but you can explicitly specify it using PARAMETERS ('SYNC (ON COMMIT)'.

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

Example 28-19 Creating a JSON Search Index That Is Synchronized Each Second

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

Example 28-20 Execution Plan Indication that a JSON Search Index Is Used

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

Ad Hoc Queries of JSON Data

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

Example 28-21 Some Ad Hoc JSON Queries

This query selects documents that contain a shipping instructions address that includes a country.

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

This query selects documents that contain user AKHOO where there are more than 8 items ordered. It takes advantage of numeric-range indexing.

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

This query selects documents where the user is AKHOO. It uses json_value instead of json_exists in the WHERE clause.

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

See Also:

• Oracle Text Reference for information about the PARAMETERS clause for CREATE SEARCH INDEX

• Oracle Text Reference for information about the PARAMETERS clause for ALTER INDEX ... REBUILD

• CREATE INDEX in Oracle Text Reference for information about synchronizing a JSON search index

• Oracle Text Application Developer's Guide for guidance about optimizing and tuning the performance of a JSON search index