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.
◉ 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>
Source: oracle-base.com
Posts
0 comments:
Post a Comment