Skip to Main Content
  • Questions
  • Additonal help with JSON object - Add/Remove items from array within a JSON object

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, John.

Asked: May 24, 2025 - 8:33 pm UTC

Last updated: June 04, 2025 - 6:22 pm UTC

Version: 19C

Viewed 1000+ times

You Asked

I thought I could take what Chris showed me in my original question ( https://asktom.oracle.com/ords/asktom.search?tag=looking-for-help-with-json-object ).
However, after 2 days, I have not made any progress.

The JSON data -

{
"recall_control":
[
{
"item_id":"item1",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL3", "VAL5"]
},
{
"item_id":"item2",
"item_cd1":"CA",
"item_cd2":"AP",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2"]
},
{
"item_id":"item3",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"CO",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL3"]
},
{
"item_id":"item4",
"item_cd1":"CA",
"item_cd2":"TW",
"item_cd3":"EX",
"descr":"Description text here...",
"recall_list": ["VAL1", "VAL2", "VAL4"]
}
]
}

With Chris's help - I can get the item_id values

with jdata as ( 
  select treat ( '{
    "recall_control": [
    {
        "item_id":"item1",
        "item_cd1":"CA",
        "item_cd2":"AP",
        "item_cd3":"CO",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL3", "VAL5"]
    },
    {
        "item_id":"item2",
        "item_cd1":"CA",
        "item_cd2":"AP",
        "item_cd3":"EX",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL2"]
    },
    {
        "item_id":"item3",
        "item_cd1":"CA",
        "item_cd2":"TW",
        "item_cd3":"CO",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL2", "VAL3"]
    },
    {
        "item_id":"item4",
        "item_cd1":"CA",
        "item_cd2":"TW",
        "item_cd3":"EX",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL2", "VAL4"]
    }    
  ]
}' as json ) j from dual
)
select json_query ( j, 
   '$.recall_control[*] ? (
     exists ( @.recall_list[*] ? ( @ == "VAL3" ) )
    ).item_id' with wrapper ) items
from   jdata;


ITEMS
-----------------
["item1","item3"]



I took what he gave me and over the last 2 days I have tried to figure out the second half of my issue. How to ADD/REMOVE items from the RECALL_LIST array based on the values obtained in the original query.

I thought I could use the JSON_VALUE method to find where ITEM_ID = "item1" and create an update query to remove VAL3 from the array or add VAL6 to it. However, the query returns now rows updated.

I tried to use Chris's search advice - "You can do this by nesting search expressions, first to locate the recall_control. Then using the exists method to see search the recall_list array. e.g.:"

That didn't work.

What am I doing wrong and/or missing here??

and Chris said...

I'm a little unclear exactly what you're trying to do here. But if you want to add/remove items from an array, JSON_transform is the way (note - this was added in 19.10, so be sure you're up-to-date with your patches).

Using this, there's no need to find the item_id values first. Use the JSON path to define which items to add/remove.

For example, this removes "VAL1" from every array and adds "test" to arrays that contain VAL3:

with jdata as ( 
  select treat ( '{
    "recall_control": [
    {
        "item_id":"item1",
        "item_cd1":"CA",
        "item_cd2":"AP",
        "item_cd3":"CO",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL3", "VAL5"]
    },
    {
        "item_id":"item2",
        "item_cd1":"CA",
        "item_cd2":"AP",
        "item_cd3":"EX",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL2"]
    },
    {
        "item_id":"item3",
        "item_cd1":"CA",
        "item_cd2":"TW",
        "item_cd3":"CO",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL2", "VAL3"]
    },
    {
        "item_id":"item4",
        "item_cd1":"CA",
        "item_cd2":"TW",
        "item_cd3":"EX",
        "descr":"Description text here...",
        "recall_list": ["VAL1", "VAL2", "VAL4"]
    }    
  ]
}' as json ) j from dual
)
select json_serialize ( 
  json_transform ( j, 
    append '$.recall_control[*] ? (
              exists ( @.recall_list[*] ? ( @ == "VAL3" ) )
            ).recall_list ' = 'test',
    remove '$.recall_control[*].recall_list[*] ? ( @ == "VAL1" ) ' 
  ) returning clob pretty
)
from   jdata;

JSON_SERIALIZE(JSON_TRANSFORM(J,APPEND'$.RECALL_CONTROL[*]?(EXISTS(@.RECALL_LIST
--------------------------------------------------------------------------------
{
  "recall_control" :
  [
    {
      "item_id" : "item1",
      "item_cd1" : "CA",
      "item_cd2" : "AP",
      "item_cd3" : "CO",
      "descr" : "Description text here...",
      "recall_list" :
      [
        "VAL3",
        "VAL5"
      ]
    },
    {
      "item_id" : "item2",
      "item_cd1" : "CA",
      "item_cd2" : "AP",
      "item_cd3" : "EX",
      "descr" : "Description text here...",
      "recall_list" :
      [
        "VAL2"
      ]
    },
    {
      "item_id" : "item3",
      "item_cd1" : "CA",
      "item_cd2" : "TW",
      "item_cd3" : "CO",
      "descr" : "Description text here...",
      "recall_list" :
      [
        "VAL2",
        "VAL3",
        "test"
      ]
    },
    {
      "item_id" : "item4",
      "item_cd1" : "CA",
      "item_cd2" : "TW",
      "item_cd3" : "EX",
      "descr" : "Description text here...",
      "recall_list" :
      [
        "VAL2",
        "VAL4"
      ]
    }
  ]
}


If this is to update rows in a table, update the JSON column to the result of this (UPDATE tab SET jcol = JSON_TRANSFORM ( ... ) ). Use JSON_exists in the WHERE clause to identify rows that contain documents you want to change.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here