8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23c
In Oracle database 23c the JSON_QUERY
and JSON_VALUE
functions can include multiple predicates in a single JSON path expression, and use the PASSING
clause to support variables.
In previous versions some simple predicates were possible with the JSON_VALUE
function, but not to the extent we see in Oracle 23c.
Related articles.
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Setup
The examples in this article use the following table.
drop table if exists t1 purge; create table t1 ( id number, json_data json, constraint t1_pk primary key (id) );
We insert some test data.
insert into t1 (id, json_data) values (1, json('[ {"fruit":"apple","quantity":10}, {"fruit":"orange","quantity":12}, {"fruit":"banana","quantity":8}, {"fruit":"lime","quantity":15}, {"fruit":"lemon","quantity":11} ]')); commit;
Here is the whole of the collection displayed with pretty print.
select id, json_query(json_data, '$' returning clob pretty) as json_data from t1; ID JSON_DATA ---------- -------------------------------------------------------------------------------- 1 [ { "fruit" : "apple", "quantity" : 10 }, { "fruit" : "orange", "quantity" : 12 }, { "fruit" : "banana", "quantity" : 8 }, { "fruit" : "lime", "quantity" : 15 }, { "fruit" : "lemon", "quantity" : 11 } ] SQL>
JSON_QUERY with Predicates
We use a predicate to return data for array elements where "fruit" is set to "apple".
select id, json_query(json_data, '$[*]?(@.fruit == "apple")') as json_data from t1; ID JSON_DATA ---------- -------------------------------------------------------------------------------- 1 {"fruit":"apple","quantity":10} SQL>
We use a predicate to limit the rows returned to just those where the "fruit" element is "apple" or "orange". We are returning multiple elements, so we need to use the WITH WRAPPER
option. In this example we are also using the PASSING
clause to define variable values, but we could have hardcoded the values as before.
select id, json_query(json_data, '$[*]?(@.fruit in ($v1, $v2))' passing 'apple' as "v1", 'orange' as "v2" with wrapper) as json_data from t1; ID JSON_DATA ---------- -------------------------------------------------------------------------------- 1 [{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":12}] SQL>
In this example we reduce the data further by only displaying data where the "quantity" is greater than 11.
select id, json_query(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3)' passing 'apple' as "v1", 'orange' as "v2", 11 as "v3" with wrapper) as json_data from t1; ID JSON_DATA ---------- -------------------------------------------------------------------------------- 1 [{"fruit":"orange","quantity":12}] SQL>
If we only wanted the "quantity" value, we could append ".quantity" to the end of the path. We know this will return a single value, so we could remove the WITH WRAPPER
keywords to remove the square brackets.
select id, json_query(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3).quantity' passing 'apple' as "v1", 'orange' as "v2", 11 as "v3" with wrapper) as json_data from t1; ID JSON_DATA ---------- -------------------------------------------------------------------------------- 1 [12] SQL>
Alternatively we could move the quantity predicate across to the "quantity" element and achieve the same result. This demonstrates the use of multiple predicates in a single JSON path expression.
select id, json_query(json_data, '$[*]?(@.fruit in ($v1, $v2)).quantity?(@ > $v3)' passing 'apple' as "v1", 'orange' as "v2", 11 as "v3" with wrapper) as json_data from t1; ID JSON_DATA ---------- -------------------------------------------------------------------------------- 1 [12] SQL>
JSON_VALUE with Predicates
We use a predicate to return a value from where the array where "fruit" is set to "apple".
column fruit format a30 select id, json_value(json_data, '$[*].fruit?(@ == "apple")') as fruit from t1; ID FRUIT ---------- ------------------------------ 1 apple SQL>
We repeat the previous example, but this time add the PASSING
clause to define a variable value to use in the JSON path expression.
select id, json_value(json_data, '$[*].fruit?(@ == $v1)' passing 'apple' as "v1") as fruit from t1; ID FRUIT ---------- ------------------------------ 1 apple SQL>
In this example we could return data for "apple" or "orange", but only where the "quantity" is greater than 11.
select id, json_value(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3).fruit' passing 'apple' as "v1", 'orange' as "v2", 11 as "v3") as fruit from t1; ID FRUIT ---------- ------------------------------ 1 orange SQL>
If we only wanted the "quantity" value, we could append ".quantity" to the end of the path. This time we add a returning clause to convert the result into a number.
select id, json_value(json_data, '$[*]?(@.fruit in ($v1, $v2) && @.quantity > $v3).quantity' passing 'apple' as "v1", 'orange' as "v2", 11 as "v3" returning number) as quanity from t1; ID QUANITY ---------- ---------- 1 12 SQL>
Alternatively we could move the quantity predicate across to the "quantity" element and achieve the same result. This demonstrates the use of multiple predicates in a single JSON path expression.
select id, json_value(json_data, '$[*]?(@.fruit in ($v1, $v2)).quantity?(@ > $v3)' passing 'apple' as "v1", 'orange' as "v2", 11 as "v3" returning number) as quanity from t1; ID QUANITY ---------- ---------- 1 12 SQL>
For more information see:
- SQL/JSON Function JSON_QUERY
- SQL/JSON Function JSON_VALUE
- Basic SQL/JSON Path Expression Syntax
- JSON Support Enhancements in Oracle Database 23c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23c, All Articles
Hope this helps. Regards Tim...