update json value in oracle

10,411

Solution 1

You could use JSON_ELEMENT_T.parse and <json_obj>.PUT method within PL/SQL.

Setup

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

insert into tjson (jsoncol) VALUES ( '{"y" : "4", "n" : "0" , "default" : "4"}');

PL/SQL block

DECLARE
    v_jsoncol       tjson.jsoncol%TYPE;
    v_json_obj      json_object_t;
    v_new_jsoncol   tjson.jsoncol%TYPE;
BEGIN
    SELECT
        jsoncol
    INTO v_jsoncol
    FROM
        tjson; --use appropriate where clause

    v_json_obj := TREAT(json_element_t.parse(v_jsoncol) AS json_object_t);
    v_json_obj.put('n',2); --modifies the value
    v_new_jsoncol := v_json_obj.to_string; --converts to string

    UPDATE tjson
    SET
        jsoncol = v_new_jsoncol; --update ( use appropriate where clause)

END;
/

Modified data

select jsoncol FROM tjson;

JSONCOL                                                                         
--------------------------------------------------------------------------------
{"y":"4","n":2,"default":"4"}

EDIT:

Starting Oracle 19c, You may use json_mergepatch

select json_mergepatch (jsoncol, '{"n" : "2"}') as new_json from tjson;
NEW_JSON
{
"y" : "4",
"n" : "2",
"default" : "4"
}

Live SQL demo

Solution 2

There is a way to update JSON values via simple SQL. For example to remove from all records one of json's fields:

UPDATE some_table SET json_content = JSON_MERGEPATCH(json_content, '{"someKey":null}')

Then someKey should be removed from any json stored in column json_content. Of course instead of null there can be another value to which we want to update.

Share:
10,411
Tom J Muthirenthi
Author by

Tom J Muthirenthi

Updated on December 05, 2022

Comments

  • Tom J Muthirenthi
    Tom J Muthirenthi over 1 year

    I have a column in the table which stores data in json format.

    Eg. column:

    select jsoncol from table; 
    

    -- It gives

    {"y" : "4", "n" : "0" , "default" : "4"}
    

    When i try

    select json_value(jsoncol, '$.n') from table;
    

    I get the value 0.

    How can I update the value of n as 2,

    {"y" : "4", "n" : "2" , "default" : "4"}
    
  • a_horse_with_no_name
    a_horse_with_no_name over 4 years
    Is there any way to do this in plain SQL (e.g. a simple UPDATE statement to remove a specific key from a JSON value) with Oracle 18c?
  • Kaushik Nayak
    Kaushik Nayak over 4 years
    @a_horse : Not something that I could find. Glad to know though if there are any.