8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
JSON_TRANSFORM in Oracle Database 21c
The JSON_TRANSFORM
function was introduced in Oracle database 21c to simplify the modification of JSON data.
Oracle database 19c introduced the JSON_MERGEPATCH function for updating JSON documents. The JSON_TRANSFORM
function has more functionality, and is easier to use.
- Setup
- SET Operation
- INSERT Operation
- APPEND Operation
- REMOVE Operation
- RENAME Operation
- REPLACE Operation
- KEEP Operation
- Combining Multiple Operations
- Direct Updates Using JSON_TRANSFORM
- RETURNING Clause
- PL/SQL Support
Related articles.
- JSON_TRANSFORM in Oracle Database 21c
- JSON Data Type in Oracle Database 21c
- JSON_MERGEPATCH in Oracle Database 19c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
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 VARCHAR2
, CLOB
or BLOB
.
-- drop table t1 purge; create table t1 ( id number, json_data json, constraint t1_pk primary key (id) );
We insert two rows of test data.
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;
From the output below we can see row 1 contains a flat JSON object, and row 2 contains an array of JSON objects.
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>
SET Operation
The following SET
operation updates the quantity value from 10 to 20. The output is returned as a CLOB
using the RETURNING
clause, with the PRETTY
keyword to pretty-printed the output.
select json_transform(json_data, set '$.quantity' = 20 returning clob pretty) as data from t1 where id = 1; DATA -------------------------------------------------------------------------------- { "fruit" : "apple", "quantity" : 20 } SQL>
If we use SET
to amend an element that isn't already present, the default operation is to create it. Here we use the SET
operation to add a new element called "updated_date".
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>
We can use complex JSON object values using the JSON
constructor or FORMAT JSON
. 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.
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>
The SET
operation works equally well for arrays, as shown in the following examples.
-- 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>
The default behaviour of the SET
operation can be altered using the following handlers.
REPLACE ON EXISTING
(default),ERROR ON EXISTING
,IGNORE ON EXISTING
CREATE ON MISSING
(default),ERROR ON MISSING
,IGNORE ON MISSING
NULL ON NULL
(default),ERROR ON NULL
,IGNORE ON NULL
,REMOVE ON NULL
For example, to raise an error if we try to amend an item that isn't present, we would do the following.
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>
Many of the following operations can be replicated using the SET
operation with the correct handlers.
INSERT Operation
The INSERT
operation is used to add a new element which doesn't already exist. Here we use the INSERT
operation to add a new element called "updated_date".
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>
This differs from the default SET
operation, in that is produces an error if the item already exists. Here we use the INSERT
operation to add a new element called "fruit". Since this element already exists, it produces an error.
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>
So it's similar to using the SET
operation with the ERROR ON EXISTING
handler.
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>
The INSERT
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.
-- 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>
The default behaviour of the INSERT
operation can be altered using the following handlers.
ERROR ON EXISTING
(default),IGNORE ON EXISTING
,REPLACE ON EXISTING
NULL ON NULL
(default),ERROR ON NULL
,IGNORE ON NULL
,REMOVE ON NULL
APPEND Operation
The APPEND
operation is used to add a new element to the end of an array. Here we use the APPEND
operation to add a new "fruit" at the end of the "produce" array.
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>
This is similar to using the INSERT
operation with the [last+1]
position.
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>
The default behaviour of the APPEND
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
REMOVE Operation
The REMOVE
operation is used to delete an element from an object or an array. Here we use the REMOVE
operation to delete the "quantity" element from an object, from an object in an array, and remove a "fruit" from the "produce" array.
-- 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>
The default behaviour of the REMOVE
operation can be altered using the following handlers.
IGNORE ON MISSING
(default),ERROR ON MISSING
RENAME Operation
The RENAME
operation is used to rename an element. Here we use the RENAME
operation to rename the "fruit" element to "fruit_name".
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>
The default behaviour of the RENAME
operation can be altered using the following handlers.
IGNORE ON MISSING
(default),ERROR ON MISSING
REPLACE Operation
The REPLACE
operation is used to update the value of an element. Here we use the REPLACE
operation to update the "quantity" value from 10 to 20.
select json_transform(json_data, replace '$.quantity' = 20 returning clob pretty) as data from t1 where id = 1; DATA ------------------------------------------------------------ { "fruit" : "apple", "quantity" : 20 } SQL>
If the element doesn't exist the operation is ignored. A new element is not created. Here we use the REPLACE
operation to update the "updated_date" value. The "updated_date" element doesn't exist, so no action is taken.
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>
This is similar to using the SET
operation with the IGNORE ON MISSING
handler.
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>
The default behaviour of the REPLACE
operation can be altered using the following handlers.
IGNORE ON MISSING
(default),ERROR ON MISSING
,CREATE ON MISSING
NULL ON NULL
(default),ERROR ON NULL
,IGNORE ON NULL
,REMOVE ON NULL
KEEP Operation
The KEEP
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.
-- 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>
Combining Multiple Operations
Multiple operations can be combined into a single JSON_TRANSFORM
call. They are processed in order, and if one operation fails they all fail.
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>
Direct Updates Using JSON_TRANSFORM
All the examples so far have performed transformations on the fly as part of SELECT
statements, but we could just as easily do the transformations as part of an UPDATE
statement.
-- 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;
RETURNING Clause
The output of the JSON_TRANSFORM
function depends on the input expression. If they input is a JSON
data type, the output is also a JSON
data type. All other input types result in a VARCHAR2(4000)
return value.
The RETURNING
clause allows the output to be converted to JSON
, BLOB
, CLOB
or VARCHAR2
. Most of the examples above use RETURNING CLOB
to make the output readable.
PL/SQL Support
There is no PL/SQL support for direct assignments using the JSON_TRANSFORM
function. The following attempt results in an error.
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.
An assignment can be made using a SELECT ... INTO ... FROM DUAL
statement, as shown here.
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>
For more information see:
- JSON_TRANSFORM in Oracle Database 21c
- JSON Data Type in Oracle Database 21c
- JSON_MERGEPATCH in Oracle Database 19c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, All Articles
Hope this helps. Regards Tim...