DBA Hub

📋Steps in this guide1/12

JSON_TRANSFORM Enhancements in Oracle Database 23ai/26ai

In Oracle 23ai/26ai the JSON_TRANSFORM function has lots of new operations, conditional control and richer path support.

oracle 23configurationintermediate
by OracleDba
16 views
1

Setup

The examples in this article use the following table. We are using the JSON data type, introduced in Oracle database 21c. We could have used any supported data type, including , or . We insert two rows of test data. From the output below we can see row 1 contains a flat JSON object, and row 2 contains an array of JSON objects.

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
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}')),
  (2, json('{"produce":[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]}'));
commit;

set linesize 100 pagesize 1000 long 1000000
column data format a60

select id, json_serialize(json_data pretty) as data
from   t1;

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

         2 {
             "produce" :
             [
               {
                 "fruit" : "apple",
                 "quantity" : 10
               },
               {
                 "fruit" : "orange",
                 "quantity" : 15
               }
             ]
           }

SQL>
2

PREPEND Operation

The operation adds a new element to the start of an array. It's similar to the operation, but adds the element to the other side of the array. In the following example we and entry for "banana" into the array. This is similar to using the operation with the position. The default behaviour of the operation can be altered using the following handlers. - ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING - NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL

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 json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"banana","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "banana",
      "quantity" : 20
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

select json_transform(json_data,
                      insert '$.produce[0]' = json('{"fruit":"banana","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "banana",
      "quantity" : 20
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>
3

COPY Operation

The operation replaces the contents of a JSON array with the value provided. In the following example we replace the contents of the array with an entry for "lime". The default behaviour of the operation can be altered using the following handlers. - CREATE ON MISSING (default), IGNORE ON MISSING, ERROR ON MISSING, NULL ON MISSING - NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL

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
select json_transform(json_data,
                      copy '$.produce' = json('{"fruit":"lime","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "lime",
      "quantity" : 20
    }
  ]
}


SQL>
4

MINUS Operation

The operation removes all elements from an array that match those listed in the right-hand side. It also removes duplicates. In the following example we use add a duplicate entry for "orange" and use to remove the "apple" entry. Not only has the "apple" entry been removed, but the duplicate of the "orange" entry has also been removed. In this example we use an array of items for the minus operation. The default behaviour of the operation can be altered using the following handlers. - ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING - NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL

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
select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"orange","quantity":15}'),
                      minus '$.produce' = json('{"fruit":"apple","quantity":10}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

select json_transform(json_data,
                      minus '$.produce' = path '$temp[*]'
                      passing json('[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
  ]
}


SQL>
5

INTERSECT Operation

The operation removes all elements from an array except those that match the right-hand side. It also removes any duplicates. In the following example we add a duplicate "apple" entry, then perform an with "apple". Not only has the "orange" entry been removed, but the duplicate of the "apple" entry has also been removed. In this example we use an array of items for the intersect operation. The default behaviour of the operation can be altered using the following handlers. - ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING - NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL

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
select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"apple","quantity":10}'),
                      intersect '$.produce' = json('{"fruit":"apple","quantity":10}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    }
  ]
}


SQL>

select json_transform(json_data,
                      intersect '$.produce' = path '$temp[*]'
                      passing json('[{"fruit":"apple","quantity":10},{"fruit":"orange","quantity":15}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>
6

UNION Operation

The operation adds missing elements to the array, and removed duplicates. In the following example we add a duplicate "apple" entry, the the entry for "lime". We can see the new entry has been added to the array, but the duplicate entry for "apple" has been removed. In this example we use an array of items for the union operation. The default behaviour of the operation can be altered using the following handlers. - ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING - NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL

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
58
59
60
61
62
63
select json_transform(json_data,
                      prepend '$.produce' = json('{"fruit":"apple","quantity":10}'),
                      union '$.produce' = json('{"fruit":"lime","quantity":12}')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "lime",
      "quantity" : 12
    }
  ]
}


SQL>

select json_transform(json_data,
                      union '$.produce' = path '$temp[*]'
                      passing json('[{"fruit":"lime","quantity":12},{"fruit":"lemon","quantity":20}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "lemon",
      "quantity" : 20
    },
    {
      "fruit" : "lime",
      "quantity" : 12
    }
  ]
}


SQL>
7

SORT Operation

We an entry for "kiwi", so it is at the start of the array, then sort the produce array using the operation. We don't specify a sorting element, so it sorts by the first element. We can also do a descending order. To identify the specific element to order by, use the clause. In this case we order by the descending quantity value. The default behaviour of the operation can be altered using the following handlers. - REPLACE ON EXISTING (default), IGNORE ON EXISTING, ERROR ON EXISTING - CREATE ON MISSING (default), IGNORE ON MISSING, ERROR ON MISSING - NULL ON NULL (default), IGNORE ON NULL, ERROR ON NULL, REMOVE ON NULL

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
select json_transform(json_data,
                      prepend '$.produce' = JSON('{"fruit":"kiwi","quantity":30}'),
                      sort '$.produce'
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "kiwi",
      "quantity" : 30
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}


SQL>

select json_transform(json_data,
                      prepend '$.produce' = JSON('{"fruit":"kiwi","quantity":30}'),
                      sort '$.produce' desc
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "kiwi",
      "quantity" : 30
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    }
  ]
}


SQL>

select json_transform(json_data,
                      prepend '$.produce' = JSON('{"fruit":"kiwi","quantity":30}'),
                      sort '$.produce' order by '$.quantity' desc
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "kiwi",
      "quantity" : 30
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "apple",
      "quantity" : 10
    }
  ]
}


SQL>
8

MERGE Operation

The operation merges the specified fields into an existing object. In this example we use an array of items for the merge operation. The default behaviour of the operation can be altered using the following handlers. - ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING - NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL

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
select json_transform(json_data,
                      merge '$' = json('{"weight":20, "lifespan":5}')
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "weight" : 20,
  "lifespan" : 5
}

select json_transform(json_data,
                      merge '$' = path '$temp[*]'
                      passing json('[{"weight":20}, {"lifespan":5}, {"food miles":1000}]') as "temp"
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "weight" : 20,
  "lifespan" : 5,
  "food miles" : 1000
}


SQL>
9

NESTED PATH Operation

The or operation allows us to define a specific path in our document, which can be referenced by subsequent operations using the "@" prefix. The "$" is still available to reference the root of the document in the right-hand side. In the following example we add 5 to the quantity of all items, and add a new entry called "weight". Notice the and operations are in parenthesis after the definition, and we use "@" to reference the path. We have performed a mathematical operation on the right-hand side of the operation using to reference an item value. In this example we use the operation to set the value of "$temp", which we later refer to using . This is similar to the union example shown previously, but this time using and instead of to pass the variable value.

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
58
59
60
61
62
select json_transform(json_data,
                      nested path '$.produce[*]'
                        (set '@.quantity' = path '@.quantity + 5',
                         insert '@.weight' = 20)
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 15,
      "weight" : 20
    },
    {
      "fruit" : "orange",
      "quantity" : 20,
      "weight" : 20
    }
  ]
}


SQL>

select json_transform(json_data,
                      set '$temp' = json('[{"fruit":"lime","quantity":12},{"fruit":"lemon","quantity":24},{"fruit":"apple","quantity":10}]'),
                      union '$.produce' = path '$temp[*]'
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    },
    {
      "fruit" : "lemon",
      "quantity" : 24
    },
    {
      "fruit" : "lime",
      "quantity" : 12
    }
  ]
}


SQL>
10

CASE Operation

The operation allows us to make operations conditional. In the following example we combine and to perform some conditional processing of our JSON. For the "apple" element we assign a weight of 10 and a lifespan of 5. For the "orange" element we assign a weight of 12. For anything else we assign a weight of null. The syntax is similar to a searched expression in SQL.

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
select json_transform(json_data,
                      nested path '$.produce[*]' (
                        case
                          when '@.fruit == "apple"' then (
                            insert '@.weight' = 10,
                            insert '@.lifespan' = 5
                          )
                          when '@.fruit == "orange"' then (
                            insert '@.weight' = 12
                          )
                          else (
                            insert '@.weight' = null
                          )
                        end
                      )
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10,
      "weight" : 10,
      "lifespan" : 5
    },
    {
      "fruit" : "orange",
      "quantity" : 15,
      "weight" : 12
    }
  ]
}


SQL>
11

Arithmetic Operations

The right-hand side expression can include arithmetic operations. In the following example we add a new item called "weight", and set the "total_weight" to "quantity" * "weight", using to access the item values.

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
select json_transform(json_data,
                      nested path '$.produce[*]'
                        (set '@.weight' = 10,
                         set '@.total_weight' = path '@.quantity * @.weight')
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10,
      "weight" : 10,
      "total_weight" : 100
    },
    {
      "fruit" : "orange",
      "quantity" : 15,
      "weight" : 10,
      "total_weight" : 150
    }
  ]
}


SQL>
12

Aggregate Functions

We can use aggregate functions against the contents of the array to produce aggregated summary information. 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
select json_transform(json_data,
                      set '$.count_entries' = path '@.produce[*].count()',
                      set '$.sum_quantity' = path '@.produce[*].quantity.sum()',
                      set '$.avg_quantity' = path '@.produce[*].quantity.avg()',
                      set '$.min_quantity' = path '@.produce[*].quantity.min()',
                      set '$.max_quantity' = path '@.produce[*].quantity.max()'
                      returning clob pretty) as data
from   t1
where  id = 2;
DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ],
  "count_entries" : 2,
  "sum_quantity" : 25,
  "avg_quantity" : 12.5,
  "min_quantity" : 15,
  "max_quantity" : 15
}


SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!