30.9 Using a Multivalue Function-Based Index
A json_exists query in a WHERE clause can
pick up a multivalue function-based index if (and only if) the data that it targets matches
the scalar types specified in the index.
A multivalue function-based index for SQL/JSON condition
json_exists targets scalar JSON values, either individually or
as elements of a JSON array. You can define a multivalue index only for JSON data
that is stored as JSON data type.
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 filter expression — matching then requires that the targeted
data satisfy that filter.
A multivalue index that is defined using a data-type conversion item
method (such as numberOnly()) that has "only" in its name can be
picked up only by json_exists queries that also use that same item
method. That is, the query must use the same item method explicitly. See
Creating Multivalue Function-Based Indexes for JSON_EXISTS for more information.
A multivalue index defined using no item method, or using a data-type
conversion item method (such as number()) that does not have
"only" in its name, can be picked up by a query that targets a scalar value
(possibly as an array element) that can be converted to the type indicated by
the item method. 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 subparts field value
that matches 730. They are discussed in terms of whether they can
pick up multivalue indexes mvi, cmvi_1, and
cmvi_2, which are defined in Creating Multivalue Function-Based Indexes for JSON_EXISTS. Conversion of JSON scalar values to SQL scalar values is specified in Table 18-2.
Example 30-17 JSON_EXISTS Query With Item Method numberOnly()
This example uses item method numberOnly() in a
WHERE clause. The query can pick up index mvi
when the path expression targets either a numeric
subparts value of 730 (e.g. subparts :
730) or an array subparts value with one or more
numeric elements of 730 (e.g. subparts:[630,
730, 690, 730]). It cannot pick up index mvi
for targeted string values of "730" (e.g.
subparts:"730" or subparts:["630", "730", 690,
"730"]).
If index mvi had instead been defined used item method
number(), then this query could pick up the index for a numeric
subparts value of 730, a string
subparts value of "730", or an array
subparts value with numeric elements of 730 or
string elements of "730".
SELECT count(*) FROM parts_tab
WHERE json_exists(jparts, '$.parts.subparts?(@.numberOnly() == 730)');
Example 30-18 JSON_EXISTS Query 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
cannot be picked up by either of these queries, even if the targeted data is
the number 730. For the index to be picked up, a query must
use numberOnly(), because the index is defined using
numberOnly().
SELECT count(*) FROM parts_tab t
WHERE json_exists(jparts, '$.parts.subparts?(@.number() == 730)');
SELECT count(*) FROM parts_tab t
WHERE json_exists(jparts, '$.parts.subparts?(@ == 730)');
Example 30-19 JSON_EXISTS Query Checking Multiple Fields
The filter expression in this query specifies the existence of a
partno field that matches the SQL NUMBER value
4 (possibly by conversion from a JSON string), and a field
subparts that matches the number 730.
The query can pick up either of the indexes cmvi_1 or
cmvi_2. Both rows of the data match these indexes, because each
row has a parts.partno value that matches the number
4 and a parts.subparts value that matches the
number 730. For the subparts match, the first row
has a subparts value of 730, and the second row
has a subparts value that is an array with a value of
730.
SELECT a FROM parts_tab
WHERE json_exists(jparts,'$.parts[*]?(@.partno == 4 &&
@.subparts == 730)');
Example 30-20 JSON_EXISTS Query Checking Array Element Position
This example is similar to Example 30-19, but in addition to requiring that field partno
match the number 4, the filter expression here requires that the
value of field subparts match an array of at least two
elements, and that the second element of the array match the number
730.
This query can pick up index cmvi_2, including for
positional predicate [1]. Index cmvi_2 specifies
virtual column subpartNum, which corresponds to JSON field
subparts, as the penultimate column, just before the final,
FOR ORDINALITY, column.
This query could also pick up index cmvi_1, but that index has no
FOR ORDINALITY column, so making use of it would require an
extra step, to evaluate the array-position condition, [1]. Using
index cmvi_2 requires no such extra step, so it is more performant
for such queries.
SELECT a FROM parts_tab
WHERE json_exists(jparts,'$.parts[*]?(@.partno == 4 &&
@.subparts[1] == 730)');