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) INCLUDE ($.PONumber, $.LineItems.Part.UnitPrice)
VALUE(VARCHAR2) INCLUDE ($.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';
See Also:
-
CREATE SEARCH INDEX in Oracle Text Reference for information about the
PARAMETERS
clause forCREATE SEARCH INDEX
, including the use of path subsetting -
ALTER INDEX PARAMETERS Syntax in Oracle Text Reference for information about the
PARAMETERS
clause forALTER INDEX
...REBUILD
-
Using Automatic Maintenance for an Index in Oracle Text Application Developer's Guide
-
CREATE INDEX in Oracle Text Reference for information about synchronizing a JSON search index
-
CREATE_PATH_LIST and DROP_PATH_LIST in Oracle Text Reference for information about creating and dropping a path list for path subsetting
-
ADD_PATH in Oracle Text Reference for information about adding paths to a path list, and dropping a path list, for path subsetting
-
CTX_USER_INDEXES in Oracle Text Reference for information about the properties of existing Oracle Text Indexes
-
Optimizing the Index in Oracle Text Application Developer's Guide for guidance about optimizing and tuning the performance of a JSON search index