Using a Multivalue Index

A query with a JSON_EXISTS condition in the WHERE clause can pick up a multivalue index if, and only if, the data that JSON_EXISTS targets matches the scalar types specified in the index.

A multivalue index for JSON_EXISTS targets scalar JSON values, either individually or as elements of a JSON array. You can only define a multivalue index for data stored as JSON data type. See Creating Multivalue Indexes for JSON_EXISTS.

These examples query the table defined and populated in Example 4-12. The examples use JSON_EXISTS in a WHERE clause to check for a subparts field value that matches 730. The examples discuss if the queries can pick the mvi, cmvi_1, and cmvi_2 multivalue indexes, which are defined in Example 4-13, Example 4-14, and Example 4-15, respectively. Conversion of JSON scalar values to SQL scalar values is specified in ON MISMATCH Clause for SQL/JSON Query Functions in Oracle Database JSON Developer's Guide.

Example 4-16 JSON_EXISTS Query With Item Method numberOnly()

The query in this example uses the numberOnly() item method in a WHERE clause. The query can pick up the mvi index when the path expression targets either a numeric value of 730 or an array value with one or more numeric elements of 730 in the subparts field. However, it cannot pick up the mvi index for targeted string values of "730", if there were any.

SELECT COUNT(*) FROM parts_tab 
  WHERE JSON_EXISTS(jparts, '$.parts.subparts?(@.numberOnly() == 730)');

Using the EXPLAIN ttIsql command on the query, the query optimizer plan shows that the query picks up the mvi index.

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkJsonRangeScan
  TBLNAME:             PARTS_TAB
  IXNAME:              MVI
  INDEXED CONDITION:   PARTS_TAB.JPARTS.parts.subparts = 730
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2


  STEP:                2
  LEVEL:               1
  OPERATION:           OneGroupGroupBy
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS:

Example 4-17 JSON_EXISTS Query Without Item Method numberOnly()

Neither of the queries in this example use the numberOnly item method. The first query uses the number() item method—which converts the targeted data to a number, if possible. The second query does no conversion type of the target data.

Neither query can pick up the mvi index, since the item method defined in the index is numberOnly(). For a query to pick up this index, the item method in the query must be 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 4-18 JSON_EXISTS Query Checking Multiple Fields

The query in this example uses a filter expression that specifies the existence of a partno field that matches the SQL NUMBER value of 4 (possibly by conversion from a JSON string), and a subparts field that matches the value of 730.

The query can pick up either of the cmvi_1 or cmvi_2 indexes. Both rows of data match these indexes, because each row has a parts.partno value that matches the number value of 4 and a parts.subparts value that matches the number value of 730. For the subparts match, the first row has a value of 730, and the second row has a value that is an array with a value of 730.

SELECT 'a' AS a FROM parts_tab
  WHERE JSON_EXISTS(jparts,'$.parts[*]?(@.partno == 4 &&
                                        @.subparts == 730)');

Using the EXPLAIN ttIsql command on the query, the query optimizer plan shows that the query picks up the cmvi_2 index.

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkJsonRangeScan
  TBLNAME:             PARTS_TAB
  IXNAME:              CMVI_1
  INDEXED CONDITION:   PARTS_TAB.JPARTS.parts[*].partno = 4 AND PARTS_TAB.JPARTS.parts[*].subparts[*] = 730
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2

Example 4-19 JSON_EXISTS Query Checking Array Element Position

The query in this example is similar to Example 4-18, except the filter expression requires that the value of the subparts field matches an array of at least two elements and that the second element of the array matches the value of 730.

This query can pick up the cmvi_2 index. The index specifies a subpartNum virtual column—which corresponds to the subparts field—as the second-to-last column, just before the FOR ORDINALITY column.

This query could also pick up the cmvi_1 index, but that index has no FOR ORDINALITY column, so making use of it would require an extra step, to evaluate the [1] array-position condition. Using cmvi_2 index requires no such extra step, so it provides better performance for the query.

SELECT 'a' AS a FROM parts_tab
  WHERE JSON_EXISTS(jparts,'$.parts[*]?(@.partno == 4 &&
                                        @.subparts[1] == 730)');

Using the EXPLAIN ttIsql command on the query, the query optimizer plan shows that the query picks up the cmvi_2 index.

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkJsonRangeScan
  TBLNAME:             PARTS_TAB
  IXNAME:              CMVI_2
  INDEXED CONDITION:   PARTS_TAB.JPARTS.parts[*].partno = 4 AND 
                       PARTS_TAB.JPARTS.parts[*].subparts[*] = 730 AND 
                       PARTS_TAB.SEQ = 1
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2