Indexing Multiple JSON Fields Using a Composite Index

To index multiple fields of a JSON object, you can create a composite index using multiple JSON_VALUE path expressions or dot-notation syntax.

Alternatively, you can create virtual columns for the fields you want to index, and then create a composite index on those virtual columns. In that case, a query that references the virtual columns or the corresponding fields picks up the composite index. The query performance is the same in both cases.

The data does not depend logically on any indexes implemented to improve query performance. To see this independence reflected in your queries, query the data directly, not virtual columns. This ensures that the query behaves the same with or without an index. The index serves only to improve performance.

Example 4-20 Creating a Composite Index for JSON Object Fields

This example creates a composite index that indexes the values of the User and CostCenter fields. The index includes the RETURNING VARCHAR2 clause to constrain the length of the indexed values of the User and CostCenter fields to 20 and 6 characters, respectively.

CREATE INDEX user_cost_ctr_idx ON 
  j_purchaseorder(JSON_VALUE(po_document, '$.User'
                             RETURNING VARCHAR2(20)),
                  JSON_VALUE(po_document, '$.CostCenter'
                             RETURNING VARCHAR2(6)));

Example 4-21 Querying JSON Data Indexed with a Composite Index

The query in this example uses the index in Example 4-20 because the data type and effective path expressions matches the data type and path expression used in the index for one or both of the User and CostCenter fields.

SELECT po_document FROM j_purchaseorder
  WHERE JSON_VALUE(po_document, '$.User') = 'ABULL'
    AND JSON_VALUE(po_document, '$.CostCenter') = 'A50';

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

Query Optimizer Plan (from Query Compilation):

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkJsonRangeScan
  TBLNAME:             J_PURCHASEORDER
  IXNAME:              USER_COST_CTR_IDX
  INDEXED CONDITION:   J_PURCHASEORDER.PO_DOCUMENT.User = 'ABULL' AND 
                       J_PURCHASEORDER.PO_DOCUMENT.CostCenter = 'A50'
  NOT INDEXED:         
  MISCELLANEOUS:       cardEst = 2