Using a JSON_VALUE Index with JSON_TABLE Queries

An index using the JSON_VALUE function with the ERROR ON ERROR clause can be used for a query involving the JSON_TABLE function. The index acts as a constraint on the indexed path, to ensure that one non-null scalar JSON value is projected for each item in the JSON data.

For the index to be used by the query, it must fulfill these conditions:

• The WHERE clause of the query refers to a column projected by JSON_TABLE.

• The data type of that column matches the data type used in the index definition.

• The effective SQL/JSON path expression that targets that column matches the path expression of the index.

Note:

A JSON_VALUE index (created using dot-notation or a JSON_VALUE expression) can be picked up by a JSON_TABLE query only if the WHERE clause corresponds to a SQL comparison condition, such as >=. In particular, the index is not picked up for the IS NULL or IS NOT NULL condition. See Comparison Predicate in Oracle TimesTen In-Memory Database SQL Reference.

Example 4-4 Using a JSON_VALUE Index With a JSON_TABLE Query

The query in this example uses the index in Example 4-2 because data type and effective path expression of the po_number column matches the data type and path expression used in the index—NUMBER and $.PONumber, respectively.

SELECT jt.* FROM j_purchaseorder,
  JSON_TABLE(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;

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:               2
  OPERATION:           RowLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              PO_NUM_IDX2
  INDEXED CONDITION:   J_PURCHASEORDER.PO_DOCUMENT.PONumber = 1600
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2


  STEP:                2
  LEVEL:               2
  OPERATION:           JsonTblScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         JT.PO_NUMBER = 1600
  MISCELLANEOUS:       cardEst = 2


  STEP:                3
  LEVEL:               1
  OPERATION:           NestedLoop
  TBLNAME:             
  IXNAME:              
  INDEXED CONDITION:   
  NOT INDEXED:         
  MISCELLANEOUS: