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??
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.