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"
}
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.
Author by
Tom J Muthirenthi
Updated on December 05, 2022Comments
-
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 over 4 yearsIs 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 over 4 years@a_horse : Not something that I could find. Glad to know though if there are any.