According to this search result (Bing) here is an example of updating the skills array for a given id within a JSON object -
{
"employees": [
{
"id": 1,
"name": "Alice",
"department": "HR",
"skills": ["communication", "recruitment"]
},
{
"id": 2,
"name": "Bob",
"department": "IT",
"skills": ["programming", "database"]
}
]
}
UPDATE employees
SET employee_data = JSON_TRANSFORM(
employee_data,
APPEND '$.employees[?(@.id == 2)].skills' = 'cloud computing'
)
WHERE JSON_EXISTS(employee_data, '$.employees[?(@.id == 2)]');
However, this does not work for my situation. Below is my JSON object –
{
"recall_control": [
{
"item_id": "item1",
"descr": "Description text here...",
"recall_list": [
"VAL1",
"VAL3",
"VAL5"
]
},
{
"item_id": "item2",
"descr": "Description text here...",
"recall_list": [
"VAL1",
"VAL2"
]
},
{
"item_id": "item3",
"descr": "Description text here...",
"recall_list": [
"VAL1",
"VAL2",
"VAL3"
]
},
{
"item_id": "item4",
"descr": "Description text here...",
"recall_list": [
"VAL1",
"VAL2",
"VAL4"
]
}
]
}
Very similar to the search results (Bing) but I cannot get it to update a single array. The best I have been able to do so far is update (remove) the VAL3 entry from both objects. Even when I try this where clause - WHERE JSON_EXISTS(recall_actions, '$.recall_control[*]?(@.item_id == "item1")'); It removes it from ITEM1 & ITEM3.
Using Oracle 19C
Can someone tell me what I am missing and/or doing wrong that my update statement won't update just one object's array??
Update statement -
UPDATE recallTbl
SET recall_actions = json_transform( recall_actions, REMOVE '$.recall_control[*].recall_list[*] ?(@=="VAL3")' )
WHERE
JSON_EXISTS(recall_actions,'$.recall_control[*]?(@.item_id == "item1")')
If I include an ITEM_ID value, then I only want to update the single JSON object.