How to update JSON column with new attribute value?

user_10915384 0
Viewed 2605

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

oracle12c sql-update oracle sql json
2 Answers

Before json_mergepatch you can use basic string functions like replace.

But you need to take care with these - formatting differences may cause these to fail. It's also possible you can update multiple attributes which match your criteria.

You can do safely in pure SQL by:

  • Converting the JSON object to rows-and-columns with json_table
  • Reconstructing the document with json_object(agg) and json_array(agg), passing the new values as needed.

For example:

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"}'
);

commit;

select json_object (
  'name' value j.name,
  'LName' value j.LName,
  'salary' value 30000, -- put new salary here
  'Age' value j.Age
) 
from   tjson, json_table (
  jsoncol, '$'
  columns (
    name path '$.name',
    LName path '$.LName',
    Age int path '$.Age'
  )
) j
where  j.name = 'Kunal';

JSON_OBJECT('NAME'VALUEJ.NAME,'LNAME'VALUEJ.LNAME,'SALARY'VALUE30000,--PUTNEWSALARYHERE'AGE'VALUEJ.AGE)   
{"name":"Kunal","LName":"Vohra","salary":30000,"Age":25}  

select t.jsoncol.name, t.jsoncol.salary
from   tjson t;

NAME    SALARY   
Kunal    10000     
Rahul    20000     


update tjson t
set    jsoncol = (
  select json_object (
    'name' value j.name,
    'LName' value j.LName,
    'salary' value 30000, -- put new salary here
    'Age' value j.Age
  ) 
  from   tjson, json_table (
    jsoncol, '$'
    columns (
      name path '$.name',
      LName path '$.LName',
      Age int path '$.Age'
    )
  ) j
  where t.jsoncol.name = j.name
)
where t.jsoncol.name = 'Kunal';

select t.jsoncol.name, t.jsoncol.salary
from   tjson t;

NAME    SALARY   
Kunal    30000     
Rahul    20000

Clearly this is... cumbersome! It's impractical for complex documents.

Luckily from 12.2 you can manipulate a JSON document using PL/SQL object types:

declare
  jdoc tjson.jsoncol%type;
  jobj json_object_t;
begin
  select t.jsoncol
  into   jdoc
  from   tjson t
  where  t.jsoncol.name = 'Kunal';

  jobj := json_object_t.parse ( jdoc );
  jobj.put ( 'salary', 40000 );
  jdoc := jobj.to_clob();

  update tjson t
  set    jsoncol = jdoc
  where  t.jsoncol.name = 'Kunal';
end;
/

select t.jsoncol.name, t.jsoncol.salary
from   tjson t;

NAME    SALARY   
Kunal    40000     
Rahul    20000
user_14859550

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

user_58413060
Related