Как обновить в столбце JSON значение атрибута?

Question

Появилась необходимость динамически обновлять столбец JSON в таблице.

Такая структура данных:

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

insert into tjson VALUES ('{"name" : "Kunal", "lname" : "Vohra" , 
                            "salary" : "10000", "Age" : "25"}');
insert into tjson VALUES ('{"name" : "Rahul", "lname" : "Sharma" , 
                            "salary" : "20000", "Age" : "35"}');

Теперь нужно изменить значение salary для всей таблицы на основе какого-либо условия.
Пробовал так:

update tjson
    set jsoncol = JSON_MERGEPATCH(jsoncol, '{"salary":"'||JustChangeSalary||'"}')
where JSON_VALUE(jsoncol, '$.Age') = 25

Но JSON_MERGEPATCH доступен только с версии 19c, а используемая версии 12.2.

_{Свободный перевод вопроса update json column oracle v12.2 от участника @Kunal Vohra}

Answer 1

Вместо JSON_MERGEPATCH можно использовать SQL строковые функции, такие как replace.

Но с ними надо быть осторожным - различия в форматировании могут привести к ошибке. Также возможно, что обновятся несколько атрибутов, которые соответствуют критериям поиска.

Можно сделать это безопасно в чистом SQL:

Преобразовать объект JSON в строки и столбцы с помощью json_table.
Реконструировать документ с помощью json_object() и json_array() с новыми значениями.

Например:

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', -- здесь новое salary
            '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

Очевидно, что это громоздко и непрактично для сложных документов.

К счастью, начиная с версии 12.2 можно манипулировать JSON с помощью объектных типов PL/SQL:

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

_{Свободный перевод ответа от участника @Chris Saxon}

Answer 2

Дополню ответ уважаемого @Chris Saxon более сложным случаем манипуляци JSON с помощью объектных типов PL/SQL. Такой подход может быть полезен и в новых версиях, например, если JSON данные не находятся в таблицах, а получены с файловой системы или REST запросом.

Допустим, есть такой JSON:

insert into tjson values (
    '{item:{"name":"item 1","parts":[{"partno":1, "price":12.3}], "total":12.3}}');

Требуется добавить к товару (item) ещё одну позицию (part) и обновить полную цену.

Пример для реализации функции:

create or replace function putNewPart (jcol clob, partno int, price number) return clob is
    jobj json_object_t;
    item json_object_t;
    part json_object_t := json_object_t();
    parr json_array_t;
begin
    jobj := json_object_t.parse (jcol);
    item := jobj.get_object ('item');
    parr := item.get_array ('parts');
    part.put ('partno', partno); 
    part.put ('price', price); 
    parr.append (part);
    item.put ('total', item.get_number ('total') + price);
    return (jobj.to_clob ());
end;
/

Как воспользоваться функцией (на db<>fiddle):

update tjson t
    set jsoncol = putNewPart (jsoncol, 2, 4.56) 
where t.jsoncol.item.name = 'item 1';
  
select t.jsoncol.item.parts parts, t.jsoncol.item.total total
from tjson t
where t.jsoncol.item.name = 'item 1';

PARTS                                                            TOTAL   
---------------------------------------------------------------- --------
[{"partno":1,"price":12.3},{"partno":2,"price":4.56}]            16.86

БЛОГ НА HUSL

Как обновить в столбце JSON значение атрибута?

Ответы (2 шт):