JSON_MERGEPATCH used to modify JSON data in Oracle database

We have a table which contain JSON data as shown below:

SQL> select * from json_test;

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

Update the JSON data in the table for specific column.

SQL> Update json_test set data= JSON_MERGEPATCH(data, '{"region_name":"USA"}') where id = 102;
1 row updated.

SQL> select * from json_test;
  ID DATA
---- ------------------------------------------------------------
 101 {"region_id": "1", "region_name": "Europe"}
 102 {"region_id":"2","region_name":"USA"}
 103 {"region_id": "3"}

Insert new JSON element in the existing data

Added new element in existing JSON data like state name using Update statement as shown below:

SQL> Update json_test set data= JSON_MERGEPATCH(data, '{"state_name":"Alabama"}') where id = 102;

1 row updated.

SQL> select * from json_test;

  ID DATA
---- ------------------------------------------------------------
 101 {"region_id": "1", "region_name": "Europe"}
 102 {"region_id":"2","region_name":"USA","state_name":"Alabama"}
 103 {"region_id": "3"}

Remove the existing element from JSON data

Remove the element from existing JSON data use the null value and update that record will remove the existing value but use small letter for null otherwise get error ORA-40629: Patch specification is not valid JSON

SQL> Update json_test set data= JSON_MERGEPATCH(data, '{"state_name":null}') where id = 102;

1 row updated.

SQL> select * from json_test;

  ID DATA
---- ------------------------------------------------------------
 101 {"region_id": "1", "region_name": "Europe"}
 102 {"region_id":"2","region_name":"USA"}
 103 {"region_id": "3"}

Empty the data of JSON element

SQL> Update json_test set data= JSON_MERGEPATCH(data, '{"state_name":""}') where id = 102;

1 row updated.

SQL> select * from json_test;

  ID DATA
---- ------------------------------------------------------------
 101 {"region_id": "1", "region_name": "Europe"}
 102 {"region_id":"2","region_name":"USA","state_name":""}
 103 {"region_id": "3"}

JSON output in Pretty format

Want to see the JSON output in Pretty format: Like JSON actually representation:

SQL> select JSON_MERGEPATCH(data,'{"state_name":null}' PRETTY) from json_test;

JSON_MERGEPATCH(DATA,'{"STATE_NAME":NULL}'PRETTY)
-------------------------------------------------------------------------------
{
  "region_id" : "1",
  "region_name" : "Europe"
}

{
  "region_id" : "2",
  "region_name" : "USA"
}

{
  "region_id" : "3"
}