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

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

Full-text querying of JSON data is covered in Full-Text Search Queries. The present topic covers the creation and maintenance of JSON search indexes, which are required for full-text search and are also useful for ad hoc queries. Examples of ad hoc queries that are supported by a JSON search index are presented here.

Create a JSON search index for queries that involve full-text search. Create a JSON search index also for queries that aren't particularly expected or used regularly — that is, ad hoc queries. But to index queries for which you know the query pattern ahead of time, it's generally advisable to use a function-based index that targets such a specific pattern. If both function-based and JSON search indexes are applicable to given a query, it is the function-based index that's used.

When you create a JSON search index you can specify path subsetting, identifying the fields to include or exclude from indexing. Other fields are not indexed; the search index is not used for them when querying. This is illustrated in Example 30-26 and Example 30-27.

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) — see In-Memory JSON Data.

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 30-23 and Example 30-24 illustrate 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 is in fact picked up for that query. You will see a line similar to that shown in Example 30-25.

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 automatically maintained — it is synchronized in the background, and both text and numeric ranges are indexed for all leaf fields in a document.

If your queries that make use of a JSON search index involve only full-text search or string-equality search — they never involve string-, numeric-, or temporal-range search — then you can save some index maintenance time and disk space by specifying TEXT for parameter SEARCH_ON.

In the other direction, if you don't need full-text or string-equality search you can limit indexing to checking for value ranges of particular data types. For that, you specify VALUE for parameter SEARCH_ON.

The default value of SEARCH_ON is TEXT_VALUE, which means index for full-text and string-equality matches (TEXT) as well as string-, numeric-, and temporal-value ranges (VALUE).

By default, a JSON search index is maintained asynchronously. This can reduce the negative effect that synchronization can have on DML operations. (Until it is synchronized, an index doesn't reflect the addition or modification of data. Deletions are reflected immediately, however, even without synchronization.)

By default, a JSON search index is automatically synced in the background. You can override this behavior by modifying the index's synchronization settings for different use cases:

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 30-24 creates a search index that is synchronized on commit.
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.

Static dictionary view CTX_USER_INDEXES contains information about existing Oracle Text indexes, including JSON search indexes. For example, this query lists the synchronization and maintenance types for all Oracle Text indexes:

SELECT IDX_NAME, IDX_SYNC_TYPE, IDX_MAINTENANCE_TYPE FROM CTX_USER_INDEXES;

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 30-23 Creating a JSON Search Index with Default Behavior

This example creates a JSON search index that has the default behavior: it is automatically maintained (it is synchronized in the background), and both text and numeric ranges are indexed.

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

This code is equivalent. It uses a PARAMETERS clause to explicitly specify automatic maintenance.

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('MAINTENANCE AUTO');

Example 30-24 Creating a JSON Search Index That Is Synchronized On Commit

This example uses a PARAMETERS clause to create an index that synchronizes new data on COMMIT.

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('SYNC (ON COMMIT)');

Example 30-25 Execution Plan Indication that a JSON Search Index Is Used

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

Example 30-26 Creating a JSON Search Index with Path Subsetting for Text Search

This example creates an index for full-text and string-equality searches, but only the fields located at paths $.SpecialInstructions and $.LineItems.Part.Description are indexed.

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('SEARCH_ON
    TEXT INCLUDE ($.SpecialInstructions,
                  $.LineItems.Part.Description)');

Example 30-27 Creating a JSON Search Index with Path Subsetting for Both Text and Value Search

Like Example 30-26, this example creates an index for full-text and string-equality searches of fields $.SpecialInstructions and $.LineItems.Part.Description. But it also indexes fields $.PONumber and $.LineItems.Part.UnitPrice for numeric-value ranges, and fields $.Reference, $.User, $.ShippingInstructions.name, and $.ShippingInstructions.Address.zipCode for string-value ranges.

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('SEARCH_ON
    TEXT INCLUDE    ($.SpecialInstructions,
                     $.LineItems.Part.Description)
    VALUE(NUMBER)   ($.PONumber, $.LineItems.Part.UnitPrice)
    VALUE(VARCHAR2) ($.Reference, $.User,
                     $.ShippingInstructions.name,
                     $.ShippingInstructions.Address.zipCode)');

As an alternative, you can create the same index using parameter PATHLIST, whose value is a named list of the paths to be included, created using PL/SQL subprograms CTX_DDL.create_path_list and CTX_DDL.add_path, as follows:

BEGIN
  CTX_DDL.create_path_list('json_pl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE);
  CTX_DDL.add_path('json_pl', 'TEXT',     '$.SpecialInstructions');
  CTX_DDL.add_path('json_pl', 'TEXT',     '$.LineItems.Part.Description');
  CTX_DDL.add_path('json_pl', 'NUMBER',   '$.PONumber');
  CTX_DDL.add_path('json_pl', 'NUMBER',   '$.LineItems.Part.UnitPrice');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.Reference');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.User');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.name');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode');
END;
/
CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('PATHLIST json_pl');

Ad Hoc Queries of JSON Data

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

Example 30-28 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';