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}
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}]
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}]
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]
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]
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
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
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
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
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
For more information see:
