I have a CLOB column with the following data (simplified for the purposes of this question)
{
"notUsed": [],
"stock": [
{
"name": "eggs",
"value": "in stock"
},
{
"name": "milk",
"value": "out of stock"
}
]
}
I want to avoid having to select the entire object and parse programatically to get the data I want. Ideally I'd like to make use of the Oracle JSON path features to do this.
I want to get "value", where "name" = "eggs"
I have tried the following, but get a [99999][40442] ORA-40442: JSON path expression syntax error. I have run the above example JSON, and JSON path through an evaluator and it returns the desired result, which makes me think Oracle has it's own JSONPath intepretation
SELECT
json_query(
'{"notUsed":[],"stock":[{"name":"eggs","value":"in stock"}, {"name":"milk","value":"out of stock"}]}',
'$.stock[?(@.name=="eggs")]' )
FROM dual;
I have also tried using the Dot Notation, but haven't been able to find an example that covers adding a where clause to properties of an array.
select
myTable.id,
myTable.JSON_COLUMN.stock -- how to get array element here?
from MY_TABLE myTable
where j.id = 46
Version:
SELECT * FROM V$VERSION
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
"CORE 12.1.0.2.0 Production"
JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)
