30.6 Using a JSON_VALUE Function-Based Index with JSON_EXISTS Queries

An index created using SQL/JSON function json_value with ERROR ON ERROR can be used for a query involving SQL/JSON condition json_exists.

In order for a json_value function-based index to be picked up for one of the comparisons of the query, the type of that comparison must be the same as the returning SQL data type for the index. The SQL data types used are those mentioned for item methods double(), float(), number(), string(),timestamp(), date(), dateWithTime(), dsInterval(), and ymInterval() — see SQL/JSON Path Expression Item Methods.

For example, if the index returns a number then the comparison type must also be number. If the query filter expression contains more than one comparison that matches a json_value index, the optimizer chooses one of the indexes.

The type of a comparison is determined as follows:

  1. If the SQL data types of the two comparison terms (sides of the comparison) are different then the type of the comparison is unknown, and the index is not picked up. Otherwise, the types are the same, and this type is the type of the comparison.

  2. If a comparison term is of SQL data type string (a text literal) then the type of the comparison is the type of the other comparison term.

  3. If a comparison term is a path expression with a function step whose item method imposes a SQL match type then that is also the type of that comparison term. The item methods that impose a SQL match type are double(), float(), number(), string(), timestamp(), date(), dateWithTime(), dsInterval(), and ymInterval().

  4. If a comparison term is a path expression with no such function step then its type is SQL string (text literal).

Example 30-3 creates a function-based index for json_value on field PONumber. The index indexes NUMBER values.

Each of the queries Example 30-6, Example 30-7, and Example 30-8 can make use of this index when evaluating its json_exists condition. Each of these queries uses a comparison that involves a simple path expression that is relative to the absolute path expression $.PONumber. The relative simple path expression in each case targets the current filter item, @, but in the case of Example 30-8 it transforms (casts) the matching data to SQL data type NUMBER.

Example 30-6 JSON_EXISTS Query Targeting Field Compared to Literal Number

This query makes use of the index because:

  1. One comparison term is a path expression with no function step, so its type is SQL string (text literal).

  2. Because one comparison term is of type string, the comparison has the type of the other term, which is number (the other term is a numeral).

  3. The type of the (lone) comparison is the same as the type returned by the index: number. 

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(data, '$.PONumber?(@ > 1500)');

Example 30-7 JSON_EXISTS Query Targeting Field Compared to Variable Value

This query can make use of the index because:

  1. One comparison term is a path expression with no function step, so its type is SQL string (text literal).

  2. Because one comparison term is of type string, the comparison has the type of the other term, which is number (the other term is a variable that is bound to a number).

  3. The type of the (lone) comparison is the same as the type returned by the index: number. 

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(data, '$.PONumber?(@ > $d)'
                    PASSING 1500 AS "d");

Example 30-8 JSON_EXISTS Query Targeting Field Cast to Number Compared to Variable Value

This query can make use of the index because:

  1. One comparison term is a path expression with a function step whose item method (number()) transforms the matching data to a number, so the type of that comparison term is SQL number.

  2. The other comparison term is a numeral, which has SQL type number. The types of the comparison terms match, so the comparison has this same type, number.

  3. The type of the (lone) comparison is the same as the type returned by the index: number. 

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(data, '$.PONumber?(@.number() > $d)'
                    PASSING 1500 AS "d");

Example 30-9 JSON_EXISTS Query Targeting a Conjunction of Field Comparisons

Just as for Example 30-6, this query can make use of the index on field PONumber. If a json_value index is also defined for field Reference then the optimizer chooses which index to use for this query. 

SELECT count(*) FROM j_purchaseorder
  WHERE json_exists(data,
                    '$?(@.PONumber > 1500
                        && @.Reference == "ABULL-20140421")');

Related Topics

Parent topic: Indexes for JSON Data