Oracle JSON api -更新JSON集合中的单个记录

Question

我有CLOB字段与JSON数据:

[
{
"name": "Rahul",
"LName": "Sharma",
"salary": "20000",
"Age": "35"
},
{
"name": "Kunal",
"LName": "Vohra",
"salary": "10000",
"Age": "25"
}
]

，我只需要更新数组中一个元素的值，例如，在名称为:Kunal的记录中，我需要更改工资。

我尝试json_transform()，但这样我将每个字段的工资转换为新值。

json_transform(json_field_in_table, SET '$.salary' = 15000)

Answer 1

您可以在json_transform函数的JSON路径中使用过滤器表达式来更新特定对象:

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

RES tbody> <<tr>[{"name"Rahul","LName":"Sharma","salary":"20000","Age":"35"},{"name"Kunal","LName":"Vohra","salary":"100","Age":"25"}]

你不能使用json_transform因为json_transform, json_exists…对整个JSON文档进行评估，而不是其中的一部分，即使json_exists有"'$?(@.name == "Kunal") "将考虑整个文档的匹配，然后更新所有的"salary"字段。(https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/condition-JSON_EXISTS.html guid - 8 a0043d5 - 95 - f8 - 4918 - 9126 - f86fb0e203f0)

但是你可以:

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'
));