The JSON_TRANSFORM function has more functionality, and is easier to use.
◉ 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
◉ 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>
Posts
0 comments:
Post a Comment