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;
Parent topic: Indexes for JSON Data