Attempting to learn and make use of JSON data within a table. Stuck on the following and would really appreciate some help...
Looking for help to create a list of ITEM_IDs where a match is found in the JSON object.
Sample column data - (column: recall_control_json)
{
"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"]
}
]
}Trying to write the query that would return a list of ITEM_IDs where the RECALL_LIST contains VAL3 or getting help from others in learning why it can't be done and/or how it could be done.
So far, I have been able to return all the ITEM_ID values with this query --
select json_query(rt.recall_control_json, '$.recall_control.item_id' With wrapper) as recallList from recallTbl rt
where recall_id = 502;
RETURNS -- ["item1","item2","item3","item4"]
But I have not been able to update the where clause so that I only get the ones I need
You can search within JSON using JSON path expressions, which look roughly like:
$.path ? ( @.path )
So you want to:
- Search the elements of the recall_control array
- To see if any elements of each recall_list equal VAL3
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.:
'$.recall_control[*] ? ( exists ( @.recall_list[*] ? ( @ == "VAL3" ) ) ).item_id
Which gives:
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"]