Example 30-21 illustrates this. A SQL query that references the corresponding JSON data (object fields) picks up the composite index. Example 30-22 illustrates this.

Alternatively, you can create virtual columns for the JSON object fields you want to index, and then create a composite B-tree index on those virtual columns. In that case a SQL query that references either the virtual columns or the corresponding JSON data (object fields) picks up the composite index. The query performance is the same in both cases.

The data does not depend logically on any indexes that are implemented to improve query performance. If you want this independence from implementation to be reflected in your code, then query the data directly (not virtual columns). Doing that ensures that the query behaves the same with or without the index — the index serves only to improve performance.

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

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