Am trying to update a JSON column in oracle 12.1. Is there any json function that can be used to update the JSON. I have added a constraint JSON_COLUMN is JSON.
I have tried :
UPDATE tablename SET JSON =
json_mergepatch(JSON, '{"STRUCTURE_NAME":null}');
But this function is applicable only for 19c
This "STRUCTURE_NAME": "ABC:STATE:L12345", needs to be updated with "STRUCTURE_NAME":null
Pre-19c, if you want to change any values in a JSON document, you have to replace the whole thing:
create table t (
doc varchar2(100)
check ( doc is json )
);
insert into t values ('{
"changeMe" : "to null",
"leaveMe" : "alone"
}');
update t
set doc = '{
"changeMe" : null,
"leaveMe" : "alone"
}';
select * from t;
DOC
{
"changeMe" : null,
"leaveMe" : "alone"
}
Note that when you get to 19c and use json_mergepatch, setting an attribute to null removes it from the document:
update t
set doc = json_mergepatch (
doc,
'{
"changeMe" : null
}');
select * from t;
DOC
{"leaveMe":"alone"}
node.STATUS in your JSON! You can't use JSON path expressions in replace. You need to search for the exact text in the document you want to change. e.g. if in your example you want "TYPE": "ABCD" to become null, you need to REPLACE(JSON, '"TYPE": "ABCD"', '"TYPE": null'). Or just REPLACE(JSON, '"ABCD"', 'null') and hope "ABCD" is nowhere else in the document!Below query will overwrite the JSON document column for all rows.
UPDATE JSON_TABLE
SET JSON_DOC_COL=JSON_OBJECT('name' VALUE var_name,
'age' VALUE var_age);
To append the JSON document column, below query can be used.
UPDATE JSON_TABLE
SET JSON_DOC_COL=JSON_MERGEPATCH(JSON_DOC_COL,JSON_OBJECT('name' VALUE var_name,
'age' VALUE var_age) RETURNING CLOB);
