Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Updating a nested array within an array of JSON objects

John WalkerMay 27 2025 — edited May 27 2025

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.

This post has been answered by John Walker on May 28 2025
Jump to Answer
Comments
Post Details
Added on May 27 2025
2 comments
142 views