DBA Hub

📋Steps in this guide1/3

Predicates for JSON_QUERY and JSON_VALUE in Oracle Database 23ai/26ai

In Oracle database 23ai/26ai 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.

oracle 23configurationintermediate
by OracleDba
13 views
1

Setup

The examples in this article use the following table. We insert some test data. Here is the whole of the collection displayed with pretty print.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
drop table if exists t1 purge;

create table t1 (
  id         number,
  json_data  json,
  constraint t1_pk primary key (id)
);

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;

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>
2

JSON_QUERY with Predicates

We use a predicate to return data for array elements where "fruit" is set to "apple". 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 option. In this example we are also using the clause to define variable values, but we could have hardcoded the values as before. In this example we reduce the data further by only displaying data where the "quantity" is greater than 11. 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 keywords to remove the square brackets. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
select id,
       json_query(json_data, '$[*]?(@.fruit == "apple")') as json_data
from t1;

        ID JSON_DATA
---------- --------------------------------------------------------------------------------
         1 {"fruit":"apple","quantity":10}

SQL>

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>

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>

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>

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>
3

JSON_VALUE with Predicates

We use a predicate to return a value from where the array where "fruit" is set to "apple". We repeat the previous example, but this time add the clause to define a variable value to use in the JSON path expression. In this example we could return data for "apple" or "orange", but only where the "quantity" is greater than 11. 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. 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. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
column fruit format a30

select id,
       json_value(json_data, '$[*].fruit?(@ == "apple")') as fruit
from t1;

        ID FRUIT
---------- ------------------------------
         1 apple

SQL>

select id,
       json_value(json_data, '$[*].fruit?(@ == $v1)'
       passing 'apple' as "v1") as fruit
from t1;

        ID FRUIT
---------- ------------------------------
         1 apple

SQL>

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>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!