Modify, insert, and delete array element data JSON in Oracle

You can handle the array of JSON data with the use of JSON_TRANSFER. I will try to provide some examples of using JSON_TRANSFER for inserting, deleting, and modifying the data.

Create a table and insert some array data in JSON format in Table

create table json_test ( id number, data clob );

SQL> insert into json_test values (101,'{"region_id": "1", "region_name": ["Europe","London","India","UK"]}');

1 row created.

SQL> insert into json_test values (102,'{"region_id": "2", "region_name": ["Canada","USA"]}');

1 row created.

Update Array Element JSON data in Oracle

Update the first element present in the array by using [0] with JSON_TRANFER

SQL> select json_transform(data, SET '$.region_name[0]'='test') from json_Test;

JSON_TRANSFORM(DATA,SET'$.REGION_NAME[0]'='TEST')
--------------------------------------------------------------------------------
{"region_id":"1","region_name":["test","London","India","UK"]}
{"region_id":"2","region_name":["test","USA"]}

Insert in existing array Element JSON data in Oracle

select json_transform(data, INSERT '$.region_name[0]'='test') from json_Test;

JSON_TRANSFORM(DATA,INSERT'$.REGION_NAME[0]'='TEST')
--------------------------------------------------------------------------------
{"region_id":"1","region_name":["test","Europe","London","India","UK"]}
{"region_id":"2","region_name":["test","Canada","USA"]}

Remove or delete from JSON array element in Oracle

SQL> select json_transform(data, REMOVE '$.region_name[*]?(@ == "USA")') from json_Test;

JSON_TRANSFORM(DATA,REMOVE'$.REGION_NAME[*]?(@=="USA")')
------------------------------------------------------------------
{"region_id":"1","region_name":["Europe","London","India","UK"]}
{"region_id":"2","region_name":["Canada"]}

-- You can use as following if you have further defining name, In my case i only consist array data that why use @ only

Select 
json_transform(data,REMOVE '$.region_name[*]?(@.state == "Alabama")')
from json_test;