30.5 Using a JSON_VALUE Function-Based Index with JSON_TABLE Queries

An index created using json_value with ERROR ON ERROR can be used for a query involving json_table. In this case the index acts as a constraint on the indexed path, to ensure that only one (non-null) scalar JSON value is projected for each item in the JSON data.

For the index to be used in this way each of these conditions must hold:

  • The query WHERE clause 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 that targets that column matches the indexed path expression.

The query in Example 30-5 thus makes use of the index created in Example 30-3.

Note:

A function-based index created using a json_value expression or dot notation can be picked up for a corresponding occurrence in a query WHERE clause only if the occurrence is used in a SQL comparison condition, such as >=. In particular, it is not picked up for an occurrence used in condition IS NULL or IS NOT NULL.

See Oracle Database SQL Language Reference for information about SQL comparison conditions.

Example 30-5 Use of a JSON_VALUE Function-Based Index with a JSON_TABLE Query

The index can be picked up because the column SQL type, NUMBER(5), matches the type used in the index. 

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.data, '$'
         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;

Parent topic: Indexes for JSON Data