I have CLOB field with JSON data :
[
{
"name": "Rahul",
"LName": "Sharma",
"salary": "20000",
"Age": "35"
},
{
"name": "Kunal",
"LName": "Vohra",
"salary": "10000",
"Age": "25"
}
]
and I need update value in only one element of that array, for example in record with name: Kunal I need change salary.
I try json_transform()
but with this I transform every field salary to new value.
json_transform(json_field_in_table, SET '$.salary' = 15000)
2
Answers
You can’t use json_transform because json_transform, json_exists… evaluate on the whole JSON document not on pieces of it,
even a json_exists with "’$?(@.name == "Kunal")’" will consider that the whole document matches and then update all "salary" fields.
(https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html#GUID-8A0043D5-95F8-4918-9126-F86FB0E203F0)
but you can:
You may use filter expression in JSON path of
json_transform
function to update specific objects:fiddle
Note that
"10000"
is a string in JSON, numbers should be used without quotes:{"salary: 10000}