Home > HowTo, JSON, SQL > How To : Nest Multiple JSON Path Expression Filters

How To : Nest Multiple JSON Path Expression Filters

A short post about JSON Path Expressions.
I will focus on filter expressions (now officially documented as of Oracle 12.2) we can use in conjunction with JSON_EXISTS function, and more specifically how to nest multiple relative filter steps.

Here’s an example of what I mean :

{
  "_id":"53e3c6ed-9bfc-2730-e053-0100007f6afb",
  "content":{
    "name":"obj1", 
    "type":1, 
    "values":[
      {"name":"x", "v":1},
      {"name":"y", "v":2},
      {"name":"z", "v":1.5}
    ]
  }
}

and say we want to find documents (like the one above) where content.type = 1 and content.values contains an x item having v > 0.

 

Preliminary setup :

create table json_documents (
  id   integer
, doc  clob
, check (doc is json)
) 
lob (doc) store as securefile (cache) ;

insert into json_documents (id, doc)
values (1, 
'{
  "_id":"53e3c6ed-9bfc-2730-e053-0100007f6afb",
  "content":{
    "name":"obj1", 
    "type":1, 
    "values":[
      {"name":"x", "v":1},
      {"name":"y", "v":2},
      {"name":"z", "v":1.5}
    ]
  }
}'
);

 

First attempt :

SQL> select id
  2  from json_documents
  3  where json_exists(doc,
  4          '$.content?( @.type==1 && @.values[*]?( @.name == "x" && @.v > 0 ) )'
  5        )
  6  ;
select id
*
ERROR at line 1:
ORA-40597: JSON path expression syntax error ('$.content?( @.type==1 &&
@.values[*]?( @.name == "x" && @.v > 0 ) )')
JZN-00224: Unexpected characters in comparison operator
at position 70

OK, syntax error here.
We cannot simply filter a relative path step that’s already part of a parent filter expression :

$.content?( @.type==1 && @.values[*]?( @.name == "x" && @.v > 0 ) )

However, it seems like the exists operator is provided to do so, as shown in the below syntax diagram (from the documentation) :

json_cond_other

 

Second attempt :

SQL> variable val number
SQL> exec :val := 0

PL/SQL procedure successfully completed.

SQL> select id
  2  from json_documents
  3  where json_exists(doc,
  4          '$.content?( @.type==1 && exists( @.values[*]?( @.name == "x" && @.v > $val ) ) )'
  5          passing :val as "val"
  6        )
  7  ;

        ID
----------
         1

SQL> exec :val := 1

PL/SQL procedure successfully completed.

SQL> select id
  2  from json_documents
  3  where json_exists(doc,
  4          '$.content?( @.type==1 && exists( @.values[*]?( @.name == "x" && @.v > $val ) ) )'
  5          passing :val as "val"
  6        )
  7  ;

no rows selected

Now it’s working.
Using the exists operator allows us to nest a new sequence of relative path steps that may include a filter.

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: