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 functionjson_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 functionjson_value
. You can use the standard syntax for this, explicitly specifyingjson_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 andjson_value
queries. - Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries
An index created usingjson_value
withERROR ON ERROR
can be used for a query involvingjson_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 functionjson_value
withERROR ON ERROR
can be used for a query involving SQL/JSON conditionjson_exists
. - Data Type Considerations for JSON_VALUE Indexing and Querying
For a function-based index created using SQL/JSON functionjson_value
to be picked up for a given query, the data type returned byjson_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 asJSON
type you can use a multivalue function-based index for SQL/JSON conditionjson_exists
. Such an index targets scalar JSON values, either individually or as elements of a JSON array. - Using a Multivalue Function-Based Index
Ajson_exists
query in aWHERE
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 functionjson_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.
Parent topic: Performance Tuning for JSON
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.
Related Topics
Parent topic: Indexes 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
.
Parent topic: Indexes for JSON Data
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'));
Parent topic: Indexes for JSON Data
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 aRETURNING
data type. It can optionally useERROR ON ERROR
andNULL ON EMPTY
. The indexed values are only scalars of the data type specified by theRETURNING
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));
Parent topic: Indexes for JSON Data
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 byjson_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;
Parent topic: Indexes for JSON Data
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:
-
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.
-
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.
-
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()
, andymInterval()
. -
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:
-
One comparison term is a path expression with no function step, so its type is SQL string (text literal).
-
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).
-
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:
-
One comparison term is a path expression with no function step, so its type is SQL string (text literal).
-
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).
-
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:
-
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. -
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.
-
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")');
Related Topics
Parent topic: Indexes for JSON Data
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;
Parent topic: Indexes for JSON Data
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());
Related Topics
Parent topic: Indexes for JSON Data
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)');
Parent topic: Indexes for JSON Data
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';
Parent topic: Indexes for JSON Data
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';
Related Topics
See Also:
-
Oracle Text Reference for information about the
PARAMETERS
clause forCREATE SEARCH INDEX
-
Oracle Text Reference for information about the
PARAMETERS
clause forALTER 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
Parent topic: Indexes for JSON Data