Posts Tagged ‘JSON_EXISTS’

How To : Nest Multiple JSON Path Expression Filters

July 9, 2017 Leave a comment

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 :

      {"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.


Read more…


New JSON Path Expression Predicates

September 20, 2015 1 comment

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 :

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)


Read more…

Categories: JSON, SQL Tags: , ,