How to check if a json path/value entries exists in a huge json object in oracle sql?

user_11939250 0
Viewed 101

Let's say the json object is like :

{
    "title":"<title goes here>",
    "version":"1.0.0",
    "name":"<name goes here>",
    "features": {
        "feature-id1":{
             "id":"090ccd9c-8930-4aa9-9aa3-f9c8e6747a27",
              "name":"feature1",
              "version":"1.0.0"
        }
        "feature-id2":{
             "id":"932twe8e-2184-4er9-5qw3-g4e9w9821w71",
              "name":"feature2",
              "version":"1.0.0"
        }
      ...
     }     
}

I want to check whether there exists a json path/value entries (or) a nested json object in the above json data.
I have tried with
json_exists(json_column,'$.features?(@."feature-id1" == "{"id": "090ccd9c-8930-4aa9-9aa3-f9c8e6747a27","name":"feature1","version":"1.0.0"}")');
But this is giving me an error "JZN-00229: Missing parenthesis in paranthetical expression". Can't we use json_exists() to check for json objects/arrays with values?
In Postgresql this can be achieved by using the '@>' operator :
json_data->'features'->'feature-id1' @> 'provide the json value'
Is there a way to achieve the same in Oracle SQL? I'm using Oracle 19c with latest patch(19.15).

oracle
1 Answers

At least in Oracle (I don't know other database products - you mention Postgre and say it has capabilities that Oracle doesn't have), comparison filters like you are trying to apply only work for scalar values, not for nested objects.

If I understand what you are trying to do, then in Oracle you would do that with something like

json_exists(json_column,'$."features"."feature-id1"?(
     @."id"      == "090ccd9c-8930-4aa9-9aa3-f9c8e6747a27"
  && @."name"    == "feature1"
  && @."version" == "1.0.0"
  )'
)

although this is not exactly equivalent to what you were trying to do. Namely, this filter only ensures the existence of a key "feature-id1" with value a nested object that has at least the three keys "id", "name" and "version", with the prescribed values - but it may also have other keys. So the object value of "feature-id1" may not be equal to the prescribed object in your attempt, which has exactly three keys, no more. Will that work for you? Only you know.

Admin1
Related