Search code examples
sqljsonoraclesql-updateoracle12c

How to update JSON column with new attribute value?

I have a situation where I need to update json column dynamically in my table. See structure below

create table tjson ( jsoncol CLOB CONSTRAINT tjson_chk CHECK (jsoncol IS JSON) );    

insert into tjson (jsoncol) VALUES ( '{"name" : "Kunal", "LName" : "Vohra" , 
                                       "salary" : "10000", "Age" : "25"}');

insert into tjson (jsoncol) VALUES ( '{"name" : "Rahul", "LName" : "Sharma" , 
                                       "salary" : "20000", "Age" : "35"}');

Now I need to change salary to a dynamic value for complete table on the basis of some_condition

I can read data using JSON_VALUE(jsoncol, '$.Age') 

update tjson 
   set jsoncol = '"salary":$JustChangeSalary' 
 where some_condition;

salary is dynamic and not fixed. I am willing to change just salary.

I tried json_mergepatch but that is available only from Oracle version 19. We are using Oracle version 12.2

Solution

  • Assuming that you want to update the salary of Kunal to 15000, then use JSON_EXISTS() function in the WHERE condition to bring the record of him only, and use traditional REPLACE() function next to the SET clause with the literal excerpt containing key-value combinations related to salary as 

    UPDATE tjson 
   SET jsoncol = REPLACE( jsoncol, '"salary" : "10000"', '"salary" : "15000"' )
 WHERE JSON_EXISTS(jsoncol, '$.name?(@ == "Kunal")');

    Demo