'Updating json array of objects in Oracle

Hi I have a json column in Oracle database with a data like [{"id":100, "make":"BMW"},{"id":110,"make":"mercedes"}]..... now how can I update the make of object with id 110 to Toyota using sql/plsql..Thank you..

sqljsonoracleplsqlsql-update


Solution 1:[1]

You can use json_table() function for version 12.1.0.2+ to parse a json column. Btw, a string type column such as clob, varchar2 might be checked out by adding a check constraint for concerned string type column. So use :

update tab
   set jsdata=(select case when js.id = 110 then replace(jsdata,js.make,'toyota') end
                 from tab
                cross join
                      json_table(jsdata, '$[*]'
                         columns(make    varchar2(50) path '$.make',
                                 id      int path '$.id')) js
                where js.id = 110   )

Demo

Solution 2:[2]

Unfortunately, it is not easy to change data within an array.

create table t(
  id number primary key,
  json_ds varchar2(4000) check(json_ds is json)
);
insert into t values (1, '[{"id":100, "make":"BMW"},{"id":110,"make":"mercedes"}]');
commit;

update /*+ WITH_PLSQL */ t a
set json_ds = (
  with function update_json(p_in in varchar2) return varchar2 is
    l_ja json_array_t;
    l_po json_object_t;
    l_id number;
  begin
    l_ja := json_array_t.parse(p_in);
    for i in 0..l_ja.get_size - 1 loop
      l_po := json_object_t(l_ja.get(i));
      l_id := l_po.get_number('id');
      if l_id = 110 then
        l_po.put('make', 'Toyota');
      end if;
    end loop;
    return l_ja.to_string;
  end update_json;
  select update_json(a.json_ds) from dual
)
where id = 1;
/
select * from t;

ID  JSON_DS
1   [{"id":100,"make":"BMW"},{"id":110,"make":"Toyota"}]

Best regards, Stew Ashton

Solution 3:[3]

You can use JSON_TRANSFORM() in 19.8 and up.

with example as
 (select '[{"id":100, "make":"BMW"},{"id":110,"make":"mercedes"}]' as json from dual)
select json_transform(example.json, set '$[*]?(@.id==110).make' = 'Toyota') as newjson from example;

Output:

[{"id":100,"make":"BMW"},{"id":110,"make":"Toyota"}]

You can also use JSON_MERGEPATCH (19c and up) but it can't update within arrays, need to update the whole array

Regards

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Stew Ashton
Solution 3 Olafur Tryggvason

Related Questions