Search code examples
jsonoracleoracle12cjsonpath

How to select specific element from a JSON array in Oracles JSON CLOB type

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"
Solution

  • The JSON_path_expression only supports some basic syntax, according to the manual:

    JSON_path_expression::=

    enter image description here

    object_step::=

    enter image description here

    array_step::=

    enter image description here

    An alternative approach is to use JSON_TABLE to convert the JSON into a relational table and then project and filter the columns.

    select value
from json_table(
    '{
        "notUsed": [],
        "stock": [
            {
                "name": "eggs",
                "value": "in stock"
            },
            {
                "name": "milk",
                "value": "out of stock"
            }
        ]
    }',
    '$.stock[*]'
    columns
    (
        name varchar2(100 char) path '$.name',
        value varchar2(100 char) path '$.value'
    )
)
where name = 'eggs'

    Results:

    VALUE
-----
in stock