Using a JSON_VALUE Index with JSON_EXISTS Queries

An index using the JSON_VALUE function with the ERROR ON ERROR clause can be used for a query involving the JSON_EXISTS function.

In order for one of the comparisons in query to pick up a JSON_VALUE index, 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(), and date(). 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 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. In this case, no index is picked up. Otherwise, if the types are the same, then this type is the type of the comparison.

  • If a comparison term is a SQL string (a text literal), then the type of the comparison is the type of the other comparison term.

  • If a comparison term is path expression with a function step whose item method imposes a SQL match type, then that is the type of the that comparison term. The item methods that impose a SQL match type are: double(), float(), number(), string(), timestamp(), and date().

  • If a comparison term is a path expression without a function step whose item method imposes a SQL match type, then its type is SQL string.

Example 4-5 JSON_EXISTS Query Targeting Field Compared to Literal Number

The query in this example makes use a of JSON_VALUE index that indexes NUMBER values for the PONumber field (Example 4-2). It 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).

  • Given that one comparison term is of a SQL string, the comparison type has the type of the other term (which is number).

  • The type of the comparison is the same as the type returned by the index (number).

SELECT count(*) FROM j_purchaseorder 
  WHERE JSON_EXISTS(po_document, '$.PONumber?(@ > 1500)');

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              PO_NUM_IDX2
  INDEXED CONDITION:   J_PURCHASEORDER.PO_DOCUMENT.PONumber > 1500
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2


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

Example 4-6 JSON_EXISTS Query Targeting Field Compared to Variable Value

The query in this example makes use a of JSON_VALUE index that indexes NUMBER values for the PONumber field (Example 4-2). It 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).

  • Given that one comparison term is of a SQL string, the comparison type has the type of the other term (which is a number or a variable bound to a number, to be precise).

  • The type of the 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");

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              PO_NUM_IDX2
  INDEXED CONDITION:   J_PURCHASEORDER.PO_DOCUMENT.PONumber > 1500
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2


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

Example 4-7 JSON_EXISTS Query Targeting Field Cast to Number Compared to Variable Value

The query in this example makes use a of JSON_VALUE index that indexes NUMBER values for the PONumber field (Example 4-2). It makes use of the index because:

  • One comparison term is a path expression with a function step whose item method transform the matching data to a number, so its type is SQL number.

  • The type of the other comparison term is SQL number (variable bound to a number). Since the types of both comparison terms match, the type of the comparison is number.

  • The type of the 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");

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              PO_NUM_IDX2
  INDEXED CONDITION:   J_PURCHASEORDER.PO_DOCUMENT.PONumber > 1500
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2


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

Example 4-8 JSON_EXISTS Query Targeting a Conjunction of Field Comparisons

As with Example 4-5, the query in this example can make use of a JSON_VALUE index that indexes NUMBER values for the PONumber field (Example 4-2). If a JSON_VALUE index is also defined for the Reference field, then the optimizer chooses which index to use for the query. 

SELECT count(*) FROM j_purchaseorder
  WHERE JSON_EXISTS(po_document, '$?(@.PONumber > 1500 &&
                                     @.Reference == "ABULL-20140421")');

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              PO_REF_IDX1
  INDEXED CONDITION:   J_PURCHASEORDER.PO_DOCUMENT.Reference = 'ABULL-20140421'
  NOT INDEXED:         JSON_EXISTS(J_PURCHASEORDER.PO_DOCUMENT,'$?(@.PONumber > 1500 &&
                                     @.Reference == "ABULL-20140421")')
  MISCELLANEOUS:       cardEst = 2


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