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)

CodePudding user response：

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:

select json_arrayagg(json_object (
    'name' value name, 
    'LName' value lname, 
    'salary' value case when name = 'Kunal' then 15000 else salary end, 
    'Age' value age)) as js
from 
json_table(q'~[
  {
    "name": "Rahul",
    "LName": "Sharma",
    "salary": "20000",
    "Age": "35"
  },
  {
    "name": "Kunal",
    "LName": "Vohra",
    "salary": "10000",
    "Age": "25"
  }
]~','$[*]'
columns (
    name VARCHAR2(64) path '$.name',
    LName VARCHAR2(64) path '$.LName',
    salary NUMBER path '$.salary',
    age NUMBER path '$.Age'
));

CodePudding user response：

You may use filter expression in JSON path of json_transform function to update specific objects:

with a(col) as (
  select q'$[
  {
    "name": "Rahul",
    "LName": "Sharma",
    "salary": "20000",
    "Age": "35"
  },
  {
    "name": "Kunal",
    "LName": "Vohra",
    "salary": "10000",
    "Age": "25"
  }
]$' from dual
)
select
  json_transform(
    col,
    set '$[*]?(@.name == "Kunal").salary' = '100'
  ) as res
from a

fiddle

Note that "10000" is a string in JSON, numbers should be used without quotes: {"salary: 10000}