30.7 Data Type Considerations for JSON_VALUE Indexing and Querying

For a function-based index created using SQL/JSON function json_value to be picked up for a given query, the data type returned by json_value in the query must match the type specified in the index.

When RETURNING DATE is used with json_value, the same time-handling behavior (truncation or preservation) must be used in both the index and the query, for the index to be picked up. That is, either RETURNING DATE PRESERVE TIME must be used in both, or RETURNING DATE TRUNCATE TIME (or RETURNING DATE, since truncation is the default behavior) must be used in both.

By default, SQL/JSON function json_value returns a VARCHAR2 value. When you create a function-based index using json_value, unless you use a RETURNING clause or an item method to specify a different return data type, the index is not picked up for a query that expects a non-VARCHAR2 value.

For example, in the query of Example 30-10, json_value uses RETURNING NUMBER. The index created in Example 30-3 can be picked up for this query, because the indexed json_value expression specifies a return type of NUMBER. Without keywords RETURNING NUMBER in the index the return type it specifies would be VARCHAR2(4000) (the default) — the index would not be picked up for such a query.

Similarly, the index created in Example 30-2 can be picked up for the query because it uses item method number(), which also imposes a return type of NUMBER.

Now consider the queries in Example 30-11 and Example 30-12, which use json_value without a RETURNING clause, so that the value returned is of type VARCHAR2.

In Example 30-11, SQL function to_number explicitly converts the VARCHAR2 value returned by json_value to a number. Similarly, in Example 30-12, comparison condition > (greater-than) implicitly converts the value to a number.

Neither of the indexes of Example 30-3 and Example 30-2 is picked up for either of these queries. The queries might return the right results in each case, because of type-casting, but the indexes cannot be used to evaluate the queries.

Consider also what happens if some of the data cannot be converted to a particular data type. For example, given the queries in Example 30-10, Example 30-11, and Example 30-12, what happens to a PONumber value such as "alpha"?

For Example 30-11 and Example 30-12, the query stops in error because of the attempt to cast the value to a number. For Example 30-10, however, because the default error handling behavior is NULL ON ERROR, the non-number value "alpha" is simply filtered out. The value is indexed, but it is ignored for the query.

Similarly, if the query used, say, DEFAULT '1000' ON ERROR, that is, if it specified a numeric default value, then no error would be raised for the value "alpha": the default value of 1000 would be used.

Note:

For a function-based index based on SQL/JSON function json_value to be picked up for a given query, the same return data type and handling (error, empty, and mismatch) must be used in both the index and the query.

This means that if you change the return type or handling in a query, so that it no longer matches what is specified in the index, then you must rebuild any persistent objects that depend on that query pattern. (The same applies to materialized views, partitions, check constraints and PL/SQL subprograms that depend on that pattern.)

Example 30-10 JSON_VALUE Query with Explicit RETURNING NUMBER

SELECT count(*) FROM j_purchaseorder po
  WHERE json_value(data, '$.PONumber' RETURNING NUMBER) > 1500;

Example 30-11 JSON_VALUE Query with Explicit Numerical Conversion

SELECT count(*) FROM j_purchaseorder po
  WHERE to_number(json_value(data, '$.PONumber')) > 1500;

Example 30-12 JSON_VALUE Query with Implicit Numerical Conversion

SELECT count(*) FROM j_purchaseorder po
  WHERE json_value(data, '$.PONumber') > 1500;