Data Type Considerations for JSON_VALUE Indexing and Querying

For a given query to pick up a JSON_VALUE index, the same return data type and error handling (error, empty, or mismatch) must be used in both index and query.

For example, when RETURNING DATE is used with JSON_VALUE, the same time-handling behavior (either preserve or truncate) must be in both the index and the query for the query to pick up the index. Either RETURNING DATE PRESERVE TIME or RETURNING DATE TRUNCATE TIME (which is the same as RETURNING DATE for the latter, since truncate is the default behavior) must be used in both index and query.

By default, JSON_VALUE returns a VARCHAR2 value. A query that expects a non-VARCHAR2 value does not pick up a JSON_VALUE index that indexes non-VARCHAR2 values unless you use a RETURNING clause or an item method to specify a matching return data type.

Example 4-9 JSON_VALUE Query with Explicit RETURNING NUMBER

The query in this example uses RETURNING NUMBER. The JSON_VALUE index created in Example 4-2 can be picked up for this query, because the indexed JSON_VALUE expression specifies a NUMBER return type. Without the RETURNING NUMBER keywords in the query, the return type would be VARCHAR2 (default) and the index would not be picked up for such query.

Similarly, the index created in Example 4-1 can be picked up because it uses the number() item method to impose a NUMBER return type. Also, since the index uses the NULL ON ERROR default error-handling behavior, data that cannot be converted to number— such as a non-numeric string— in the targeted PONumber field is simply filtered out. The value is indexed, but it is ignored for the query.

Similarly, if the query uses DEFAULT '1000' ON ERROR in the JSON_VALUE expression, since it specifies a numeric default value, then no error would be raised.

SELECT COUNT(*) FROM j_purchaseorder
  WHERE JSON_VALUE(po_document, '$.PONumber' RETURNING NUMBER) > 1500;

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              PO_NUM_IDX1
  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-10 JSON_VALUE Query with Explicit Numerical Conversion

The query in this example uses the TO_NUMBER function to explicitly convert the VARCHAR2 value returned by JSON_VALUE to a number. The query might return the correct type, due to type-casting, but neither index in Example 4-1 nor Example 4-2 can be used to evaluate the query. The JSON_VALUE in the query returns VARCHAR2 values, which are then converted to number by the TO_NUMBER function.

If there is data that cannot be converted to number— such as a non-numeric string— in the targeted PONumber field, the query returns an error.

SELECT COUNT(*) FROM j_purchaseorder
  WHERE TO_NUMBER(JSON_VALUE(po_document, '$.PONumber')) > 1500;

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              J_PURCHASEORDER
  INDEXED CONDITION:   
  NOT INDEXED:         TO_NUMBER( JSON_VALUE(J_PURCHASEORDER.PO_DOCUMENT,
                                             '$.PONumber') ) > 1500
  MISCELLANEOUS:       cardEst = 2


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

Example 4-11 JSON_VALUE Query with Implicit Numerical Conversion

The query in this example uses the greater than (>) comparison condition to implicitly convert the VARCHAR2 value returned by JSON_VALUE to a number. The query might return the correct type, due to type-casting, but neither index in Example 4-1 nor Example 4-2 can be used to evaluate the query. The JSON_VALUE in the query returns VARCHAR2 values, which are then converted to number by the comparison condition.

If there is data that cannot be converted to number— such as a non-numeric string— in the targeted PONumber field, the query returns an error.

SELECT COUNT(*) FROM j_purchaseorder
  WHERE JSON_VALUE(po_document, '$.PONumber') > 1500;

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           TblLkRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              J_PURCHASEORDER
  INDEXED CONDITION:   
  NOT INDEXED:         CAST(JSON_VALUE(J_PURCHASEORDER.PO_DOCUMENT,'$.PONumber') 
                         AS NUMBER(undef,undef)) > 1500
  MISCELLANEOUS:       cardEst = 2


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