How to update, insert, remove & modify JSON data in Oracle with JSON_TRANSFORM

JSON_TRANSFORM is also useable function to insert, update, delete, remove, and modify the JSON data in Oracle. It is very straightforward and easy to use.

JSON_TRANSFORM has the following operations:

REMOVE: It removes the targeted data. (IGNORE ON MISSING)

KEEP: Remove all parts of the input data that are not targeted by at least one of the specified path expressions

RENAME: rename the field targeted (IGNORE ON MISSING)

SET: Update the existing data or insert the new one if it does not exist

REPLACE: Replace the existing data

INSERT: Insert the value to existing data

APPEND: APPEND the JSON data

Example of using JSON_TRANSFORM:

First, create a test table and insert some JSON data into it as shown below:

SQL> create table json_test ( id number, data clob );

insert into json_test values (101,'{"region_id": "1", "region_name": "Europe"}');
insert into json_test values (102,'{"region_id": "2", "region_name": "Americas"}');

SQL> select * from json_Test;

  ID DATA
---- ------------------------------------------------------------
 101 {"region_id": "1", "region_name": "Europe"}
 102 {"region_id": "2", "region_name": "Americas"}

Update the JSON data element in Oracle with JSON_TRANSFORM

You can update the region name by using the set operation as shown below:

SQL> select  json_transform(data, SET '$."region_name"' = 'LONDON') from json_test;

JSON_TRANSFORM(DATA,SET'$."REGION_NAME"'='LONDON')
-------------------------------------------------------------
{"region_id":"1","region_name":"LONDON"}
{"region_id":"2","region_name":"LONDON"}

Delete from JSON data element in Oracle with JSON_TRANSFORM

SQL> SELECT json_transform(data, REMOVE '$."region_name"' RETURNING CLOB PRETTY) FROM json_test;

JSON_TRANSFORM(DATA,REMOVE'$."REGION_NAME"'RETURNINGCLOBPRETTY)
----------------------------------------------------------------
{
  "region_id" : "1"
}

{
  "region_id" : "2"
}

Note: We can format with PRETTY as shown above in the output.

Inserting or Adding the element in JSON data in Oracle

SQL> select json_transform(data, INSERT '$.City' = 'Alabama') from json_test;

JSON_TRANSFORM(DATA,INSERT'$.CITY'='ALABAMA')
-----------------------------------------------------------------
{"region_id":"1","region_name":"Europe","City":"Alabama"}
{"region_id":"2","region_name":"Americas","City":"Alabama"}

SQL> select json_transform(data, SET '$.City' = 'Alabama' ERROR ON EXISTING) from json_test;

JSON_TRANSFORM(DATA,SET'$.CITY'='ALABAMA'ERRORONEXISTING)
----------------------------------------------------------------
{"region_id":"1","region_name":"Europe","City":"Alabama"}
{"region_id":"2","region_name":"Americas","City":"Alabama"}

REMOVE field in JSON data in Oracle

SQL> select json_transform(data, REMOVE '$.region_id') from json_test;

JSON_TRANSFORM(DATA,REMOVE'$.REGION_ID')
----------------------------------------------------------------------
{"region_name":"Europe"}
{"region_name":"Americas"}

Error handling cases in JSON_TRANSFORM Operation as:

SQL> select json_transform(data, SET '$.City' = 'Alabama' ERROR ON EXISTING) from json_test;

JSON_TRANSFORM(DATA,SET'$.CITY'='ALABAMA'ERRORONEXISTING)
----------------------------------------------------------------
{"region_id":"1","region_name":"Europe","City":"Alabama"}
{"region_id":"2","region_name":"Americas","City":"Alabama"}

Operations	Error Handling with JSON_TRANSFORM
SET	IGNORE ON MISSING (default), ERROR ON MISSING, CREATE ON MISSING, NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL, REMOVE ON NULL
REMOVE	IGNORE ON MISSING (default), ERROR ON MISSING
RENAME	IGNORE ON MISSING (default), ERROR ON MISSING
REPLACE	ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING, NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL
INSERT	ERROR ON EXISTING (default), IGNORE ON EXISTING, REPLACE ON EXISTING, NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL, REMOVE ON NULL
APPEND	ERROR ON MISSING (default), IGNORE ON MISSING, CREATE ON MISSING,NULL ON NULL (default), ERROR ON NULL, IGNORE ON NULL
KEEP	No Handler