Home > JSON, SQL > New JSON Path Expression Predicates

New JSON Path Expression Predicates

September 20, 2015 Leave a comment Go to comments

With the release of ORDS 3.0 in May this year, Oracle also included a new set of API called SODA dedicated to schemaless application development based on JSON. That enables us to use the Oracle database as a NoSQL document store.

The purpose of this post is to focus on the filter specification – aka query-by-example (QBE) – used to select JSON documents out of a collection with SODA for REST (and SODA for Java).

As explained in the documentation, a QBE may use a set of relational and boolean operators to define complex filters. Those operators include : $in, $exists, $regex, $startsWith, $and, $or etc.

Now comes the interesting part…

Each collection is actually backed up by a database table storing the JSON documents. When a query on a collection is issued via the SODA API, and specifies a QBE, Oracle translates it to a SQL query with a JSON_EXISTS predicate.
In order to handle the different kind of operators used in a QBE, the JSON Path expression syntax has been extended to support predicates.

Although it is part of the ORDS 3.0 suite, we don’t actually need to install ORDS in the database in order to use the new syntax, the prerequisite patch is sufficient :

https://support.oracle.com/rs?type=patch&id=20885778

I’ll present below an overview of this extended syntax, with some examples.
(Note that at the time I’m writting this, predicate support is still not officially documented)

 

1. Operators

Here are the available operators for predicate subexpressions, along with the QBE equivalent :

Symbol Description QBE equivalent
== equal $eq
!= not equal $ne
< less than $lt
<= less than or equal $lte
> greater than $gt
>= greater than or equal $gte
! not $not
in in (+ comma-separated list) $in
exists exists (+ field) $exists
&& and $and
|| or $or
starts with starts with (+ string) $startsWith
eq_regex match (+ regular expression) $regex

 

2. JSON Path Predicates

In a JSON path expression, a predicate may appear after an objet or array step, or just after the start symbol ($).
The syntax is as follows : ?( condition )
where condition is a boolean expression composed of field references (relative to the step the filter applies to) and operators.

The QBE-to-SQL translator prefixes field references in predicates with the ‘@’ symbol, followed by a dot (‘.’) and the field name, e.g.
?( @.fieldName == "SomeString" )
but it seems that using ‘$’ instead of ‘@’ is also supported.

I’ll use ‘@’ in the following examples.

 

3. Examples

In addition to the extended syntax for predicates, the JSON_EXISTS operator now also supports an optional PASSING clause we can use to bind a list of scalar values to the path expression.
It works the same as its XQuery counterpart XMLEXISTS (as well as XMLTABLE and XQUERY) and variables can be referenced in the path expression by prefixing them with a ‘$’ symbol.

 

a – Some sample documents :

create table json_messages (
  id      integer
, message clob     check(message is json)
);


insert into json_messages
values (1,
'{
 "str":"ABC",
 "arr":[
   {"id":1, "val":10, "opt":{"test":true}},
   {"id":2, "val":5}
 ],
 "obj":{
   "item1":{"s1":1,"s2":2},
   "item2":{"s1":1,"s2":2},
 }
}'
);

insert into json_messages
values (2,
'{
 "str":"MyPrefix-001",
 "arr":[
   {"id":1, "val":2},
   {"id":2, "val":5}
 ],
 "obj":{
   "item1":{"s1":2,"s2":0},
   "item4":"A string with some digits : 1234"
 }
}'
);

insert into json_messages
values (3,
'{
 "str":"MyPrefix-002",
 "arr":[
   {"id":1, "val":2},
   {"id":2, "val":5}
 ],
 "obj":{
   "item1":{"s1":3,"s2":2},
   "item3":[0,1,2,3,4],
   "item4":"A string with no digit"
 }
}'
);

 

b – Queries

Documents where exists an item in array ‘arr’ having ‘val’ > 5 and a ‘opt’ field :

SQL> select t.id
  2  from json_messages t
  3  where json_exists(
  4          t.message
  5        , '$.arr[*]?(@.val > $v && exists(@.opt) )'
  6          passing 5 as "v"
  7        )
  8  ;
 
             ID
---------------
              1
 

 
Documents where ‘str’ starts with “MyPrefix” :

SQL> select t.id
  2  from json_messages t
  3  where json_exists(
  4          t.message
  5        , '$?(@.str starts with $str)'
  6          passing 'MyPrefix' as "str"
  7        )
  8  ;
 
             ID
---------------
              2
              3
 

 
Documents where ‘obj.item4’ ends with one or more digits :

SQL> select t.id
  2  from json_messages t
  3  where json_exists(
  4          t.message
  5        , '$?(@.obj.item4 eq_regex $pattern)'
  6          passing '.*\d+' as "pattern"
  7        )
  8  ;
 
             ID
---------------
              2
 

 
Documents where ‘obj.item1’ has ‘s1’ equals 1 or 2 :

SQL> select t.id
  2  from json_messages t
  3  where json_exists(
  4          t.message
  5        , '$.obj.item1?(@.s1 in (1,2))'
  6        )
  7  ;
 
             ID
---------------
              1
              2
 

 
Documents where exists a ‘obj.item3’ field or ‘opt.test’ field in the first item of array ‘arr’ equals true :

SQL> select t.id
  2  from json_messages t
  3  where json_exists(
  4          t.message
  5        , '$?(exists(@.obj.item3) || @.arr[0].opt.test == true)'
  6        )
  7  ;
 
             ID
---------------
              1
              3
 

 

4. Predicates in other SQL/JSON operators

To conclude, just a few words on using predicate subexpressions within other SQL/JSON functions : JSON_VALUE, JSON_QUERY, JSON_TABLE :

They are not supported (yet?).

SQL> select json_query(t.message, '$.arr[*]?(@.id == 1)' with conditional wrapper)
  2  from json_messages t
  3  where t.id = 1 ;
from json_messages t
     *
ERROR at line 2:
ORA-40553: path expression with predicates not supported in this operation

 

Advertisements
Categories: JSON, SQL Tags: , ,
  1. Anonymous
    March 4, 2016 at 07:01

    Hi odie, Need your help.. Asked a question (How to Load XML data into Oracle Apps Table??) on Oracle community at (All Places > Development Tools > XML > PL/SQL XML Programming > Discussions ). Please help

  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: