DBA Hub

📋Steps in this guide1/12

JSON_TRANSFORM in Oracle Database 21c

The JSON_TRANSFORM function was introduced in Oracle database 21c to simplify the modification of JSON data.

oracle 21cconfigurationintermediate
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
-- drop table 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}'));
insert into t1 (id, json_data) values (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

SET Operation

The following operation updates the quantity value from 10 to 20. The output is returned as a using the clause, with the keyword to pretty-printed the output. If we use to amend an element that isn't already present, the default operation is to create it. Here we use the operation to add a new element called "updated_date". We can use complex JSON object values using the constructor or . Without these the value would just be added as an escaped string, rather than a JSON object. Here we show both methods to add a new element called "additional_info", which has a JSON object as its value. The operation works equally well for arrays, as shown in the following examples. The default behaviour of the operation can be altered using the following handlers. - (default), , - (default), , , For example, to raise an error if we try to amend an item that isn't present, we would do the following. Many of the following operations can be replicated using the operation with the correct handlers.

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
select json_transform(json_data,
                      set '$.quantity' = 20
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
--------------------------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 20
}

SQL>

select json_transform(json_data,
                      set '$.updated_date' = systimestamp
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
--------------------------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "updated_date" : "2020-12-20T15:35:36.286485Z"
}

SQL>

select json_transform(json_data,
                      set '$.additional_info' = json('{"colour":"red","size":"large"}')
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "additional_info" :
  {
    "colour" : "red",
    "size" : "large"
  }
}

SQL>


select json_transform(json_data,
                      set '$.additional_info' = '{"colour":"red","size":"large"}' format json
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "additional_info" :
  {
    "colour" : "red",
    "size" : "large"
  }
}

SQL>

-- Set quantity to 20 for first item in the produce array.
select json_transform(json_data,
                      set '$.produce[0].quantity' = 20
                      returning clob pretty) as data
from   t1
where  id = 2;

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

SQL>


-- Add the updated_date element to the first item in the produce array.
select json_transform(json_data,
                      set '$.produce[0].updated_date' = systimestamp
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10,
      "updated_date" : "2021-01-30T08:10:38.368785Z"
    },
    {
      "fruit" : "orange",
      "quantity" : 15
    }
  ]
}

SQL>


-- Add the updated_date element to all items in the produce array.
select json_transform(json_data,
                      set '$.produce[*].updated_date' = systimestamp
                      returning clob pretty) as data
from   t1
where  id = 2;

DATA
------------------------------------------------------------
{
  "produce" :
  [
    {
      "fruit" : "apple",
      "quantity" : 10,
      "updated_date" : "2021-01-30T08:10:55.828378Z"
    },
    {
      "fruit" : "orange",
      "quantity" : 15,
      "updated_date" : "2021-01-30T08:10:55.828378Z"
    }
  ]
}

SQL>

select json_transform(json_data,
                      set '$.updated_date' = systimestamp error on missing
                      returning clob pretty) as data
from   t1
where  id = 1;

Error report -
ORA-40762: missing value in JSON_TRANSFORM ()

SQL>
3

INSERT Operation

The operation is used to add a new element which doesn't already exist. Here we use the operation to add a new element called "updated_date". This differs from the default operation, in that is produces an error if the item already exists. Here we use the operation to add a new element called "fruit". Since this element already exists, it produces an error. So it's similar to using the operation with the handler. The operation can also be used to add an element to an array. In these examples we add a new "fruit" to different positions in the "produce" array. Notice this position is specified in the search path. The default behaviour of the operation can be altered using the following handlers. - (default), , - (default), , ,

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
select json_transform(json_data,
                      insert '$.updated_date' = systimestamp
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 10,
  "updated_date" : "2021-01-05T08:44:58.406618Z"
}

SQL>

select json_transform(json_data,
                      insert '$.fruit' = 'orange'
                      returning clob pretty) as data
from   t1
where  id = 1;

Error report -
ORA-40763: existing value in JSON_TRANSFORM ()

SQL>

select json_transform(json_data,
                      set '$.fruit' = 'orange' error on existing
                      returning clob pretty) as data
from   t1
where  id = 1;

ORA-40763: existing value in JSON_TRANSFORM ()

SQL>

-- Added to first position in the array.
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>


-- Added to second position in the array.
select json_transform(json_data,
                      insert '$.produce[1]' = JSON('{"fruit":"banana","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

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

SQL>


-- Appended to the end of the array.
select json_transform(json_data,
                      insert '$.produce[last+1]' = JSON('{"fruit":"banana","quantity":20}')
                      returning clob pretty) as data
from   t1
where  id = 2;

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

SQL>
4

APPEND Operation

The operation is used to add a new element to the end of an array. Here we use the operation to add a new "fruit" at the end of the "produce" array. This is similar to using the operation with the position. The default behaviour of the operation can be altered using the following handlers. - (default), ,

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

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

SQL>

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

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

SQL>
5

REMOVE Operation

The operation is used to delete an element from an object or an array. Here we use the operation to delete the "quantity" element from an object, from an object in an array, and remove a "fruit" from the "produce" array. The default behaviour of the operation can be altered using the following handlers. - (default),

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
-- Remove an element from an object.
select json_transform(json_data,
                      remove '$.quantity'
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple"
}

SQL>


-- Remove an element from an object in an array.
select json_transform(json_data,
                      remove '$.produce[0].quantity'
                      returning clob pretty) as data
from   t1
where  id = 2;

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


-- Remove an element from the "produce" array.
select json_transform(json_data,
                      remove '$.produce[1]'
                      returning clob pretty) as data
from   t1
where  id = 2;

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

SQL>
6

RENAME Operation

The operation is used to rename an element. Here we use the operation to rename the "fruit" element to "fruit_name". The default behaviour of the operation can be altered using the following handlers. - (default),

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select json_transform(json_data,
                      rename '$.fruit' = 'fruit_name'
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "quantity" : 10,
  "fruit_name" : "apple"
}

SQL>
7

REPLACE Operation

The operation is used to update the value of an element. Here we use the operation to update the "quantity" value from 10 to 20. If the element doesn't exist the operation is ignored. A new element is not created. Here we use the operation to update the "updated_date" value. The "updated_date" element doesn't exist, so no action is taken. This is similar to using the operation with the handler. The default behaviour of the operation can be altered using the following handlers. - (default), , - (default), , ,

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
select json_transform(json_data,
                      replace '$.quantity' = 20
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple",
  "quantity" : 20
}

SQL>

select json_transform(json_data,
                      replace '$.updated_date' = systimestamp
                      returning clob pretty) as data
from   t1
where  id = 1;

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

SQL>

select json_transform(json_data,
                      replace '$.updated_date' = systimestamp
                      returning clob pretty) as data
from   t1
where  id = 1;

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

SQL>
8

KEEP Operation

The operation is used to remove all elements except those included in the comma-separated list or search paths. Using the "$" search path returns an empty JSON document.

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
-- Remove everything.
select json_transform(json_data,
                      keep '$'
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
}

SQL>


-- Remove everything except the "fruit" element.
select json_transform(json_data,
                      keep '$.fruit'
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "fruit" : "apple"
}

SQL>


-- Remove everything except the "fruit" and "quantity" elements (remove nothing).
select json_transform(json_data,
                      keep '$.fruit', '$.quantity'
                      returning clob pretty) as data
from   t1
where  id = 1;

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

SQL>
9

Combining Multiple Operations

Multiple operations can be combined into a single call. They are processed in order, and if one operation fails they all fail.

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
select json_transform(json_data,
                      set '$.created_date' = systimestamp,
                      set '$.updated_date' = systimestamp,
                      rename '$.fruit' = 'fruit_type',
                      replace '$.quantity' = 20
                      returning clob pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "quantity" : 20,
  "created_date" : "2020-12-20T17:25:34.539480Z",
  "updated_date" : "2020-12-20T17:25:34.539480Z",
  "fruit_type" : "apple"
}

SQL>
10

Direct Updates Using JSON_TRANSFORM

All the examples so far have performed transformations on the fly as part of statements, but we could just as easily do the transformations as part of an statement.

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
-- Update the data directly in the table.
update t1
set    json_data = json_transform(json_data,
                                  set '$.created_date' = systimestamp,
                                  set '$.updated_date' = systimestamp,
                                  rename '$.fruit' = 'fruit_type',
                                  replace '$.quantity' = 20
                                  returning json)
where  id = 1;


-- Display the updated data.
select json_serialize(json_data pretty) as data
from   t1
where  id = 1;

DATA
------------------------------------------------------------
{
  "quantity" : 20,
  "created_date" : "2020-12-20T17:39:30.811689Z",
  "updated_date" : "2020-12-20T17:39:30.811689Z",
  "fruit_type" : "apple"
}

SQL>


rollback;
11

RETURNING Clause

The output of the function depends on the input expression. If they input is a data type, the output is also a data type. All other input types result in a return value. The clause allows the output to be converted to , , or . Most of the examples above use to make the output readable.
12

PL/SQL Support

There is no PL/SQL support for direct assignments using the function. The following attempt results in an error. An assignment can be made using a statement, as shown here. 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
set serveroutput on
declare
  l_json_in   varchar2(32767);
  l_json_out  varchar2(32767);
begin
  l_json_in  := '{"fruit":"apple","quantity":10}';

  l_json_out := json_transform(l_json_in,
                               set '$.updated_date' = systimestamp
                               returning varchar2 pretty);
  dbms_output.put_line(l_json_out);
end;
/
Error report -
ORA-06550: line 8, column 36:
PLS-00103: Encountered the symbol "$.updated_date" when expecting one of the following:

   . ( ) , * @ % & | = - + < / > at in is mod remainder not null
   rem returning with => .. <an exponent (**)> <> or != or ~= >=
   <= <> and or default like like2 like4 likec between error ||
   multiset member empty submultiset lax strict without pretty
   ascii true false absent format allow truncate
The symbol "(" was substituted for "$.updated_date" to continue.

set serveroutput on
declare
  l_json_in   varchar2(32767);
  l_json_out  varchar2(32767);
begin
  l_json_in  := '{"fruit":"apple","quantity":10}';

  select json_transform(l_json_in,
                        set '$.updated_date' = systimestamp
                        returning varchar2 pretty)
  into   l_json_out
  from   dual;
  dbms_output.put_line(l_json_out);
end;
/
{
  "fruit" : "apple",
  "quantity" : 10,
  "updated_date" : "2020-12-21T09:44:33.150459Z"
}

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!