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

27.1 Overview of Indexing JSON Data

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

You can use a B-tree index or a bitmap index for SQL/JSON function 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.

As always, such 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 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.

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. Otherwise, an error is raised at index creation time.

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.

27.2 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 27-3, an execution plan for each of the queries of Example 27-5, Example 27-6, Example 27-7, Example 27-8, and Example 27-10 references an index scan with index po_num_id1.

27.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 27-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'));

27.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 27-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 27-2 illustrates this.

The indexes created in both Example 27-3 and Example 27-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 27-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 27-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 27-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 27-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));

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

27.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 27-3 creates a function-based index for json_value on field PONumber. The index indexes NUMBER values.

Each of the queries Example 27-6, Example 27-7, and Example 27-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 27-8 it transforms (casts) the matching data to SQL data type NUMBER.

Example 27-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 27-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 27-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 27-9 JSON_EXISTS Query Targeting a Conjunction of Field Comparisons

Just as for Example 27-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")');

27.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 27-10, json_value uses RETURNING NUMBER. The index created in Example 27-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 27-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 27-11 and Example 27-12, which use json_value without a RETURNING clause, so that the value returned is of type VARCHAR2.

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

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

For Example 27-11 and Example 27-12, the query stops in error because of the attempt to cast the value to a number. For Example 27-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 27-10 JSON_VALUE Query with Explicit RETURNING NUMBER

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

Example 27-11 JSON_VALUE Query with Explicit Numerical Conversion

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

Example 27-12 JSON_VALUE Query with Implicit Numerical Conversion

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

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

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

Example 27-13 illustrates this. A SQL query that references the corresponding JSON data (object fields) picks up the composite index. Example 27-14 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 27-13 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 27-14 Querying JSON Data Indexed With a Composite Index

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

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

You create a JSON search index using CREATE SEARCH INDEX with the keywords FOR JSON. Example 27-15 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 27-17.

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 27-15 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-16 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 27-15 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 27-16 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 27-17 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 27-18 shows some non full-text queries of JSON data that also make use of the JSON search index created in Example 27-15.

Example 27-18 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: