Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table tjson (jsoncol clob constraint tjson_chk check (jsoncol is json));    
insert into tjson values (
    '{item:{"name":"item 1","parts":[{"partno":1, "price":12.3}], "total":12.3}}')
1 rows affected

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;
/
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}]
12.3


update tjson t
    set jsoncol = putNewPart (jsoncol, 2, 4.56) 
where t.jsoncol.item.name = 'item 1'
1 rows affected

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