New JSON Path Expression Predicates

September 20, 2015 Leave a 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: , ,

XQuery 3.0 : We’re getting closer…

August 27, 2015 Leave a comment

Stumbled upon this while developing a user-defined operator (for a future work related to JSON) :

SQL> select operator_name, function_name, return_type
  2  from dba_opbindings
  3  where operator_name = 'XQWINDOWSEQUENCE';
OPERATOR_NAME       FUNCTION_NAME                         RETURN_TYPE
------------------- ------------------------------------- ------------------

That is on
Yes, seems like Oracle has already been implementing some XQuery 3.0 features, namely Windowing functionalities.

This is the corresponding function definition (in SYS schema) :

create or replace function XQWindowSequenceFromXMLType(doc in sys.XMLType,
        flag in number, startexpr in varchar2, endexpr in varchar2,
        curItem in sys.XMLType, prevItem in sys.XMLType,
        nextItem in sys.XMLType, position in sys.XMLType,
        ecurItem in sys.XMLType, eprevItem in sys.XMLType,
        enextItem in sys.XMLType, eposition in sys.XMLType
        return sys.XMLSequenceType authid current_user
 pipelined using XQWindowSeq_Imp_t;

Out of curiosity, I also tried an XQuery with a window clause but as expected it has not made it into the core yet, or has it? ;)

SQL> select *
  2  from xmltable(
  3  'for tumbling window $w in (2, 4, 6, 8, 10, 12, 14)
  4      start at $s when fn:true()
  5      only end at $e when $e - $s eq 2
  6  return <window>{ $w }</window>'
  7  );
select *
ERROR at line 1:
ORA-19112: error raised during evaluation: XQuery Window Seq is not supported

Categories: XML DB, XQuery Tags: ,

JSON Update for the Impatient

July 30, 2015 1 comment

Waiting for a future(?) Oracle implementation in the database, this post presents a JSON Update API for SQL and PL/SQL.

The API comprises two parts :

  • A JSON model, using the org.json reference implementation written in Java.
    The package is used as is, except for the additional org.json.JSONArray.insert() method I’ve added to handle insert operations in arrays.
  • A JSON Path Expression parser (strict syntax), following the specifications edicted here :

All implemented updating methods are made available in SQL and PL/SQL via a dedicated package that interfaces the Java methods.

The PL/SQL package, JAR and Java source code are available here :

Read more…

Categories: JSON Tags: , , ,

How To : Parse repeating groups correlated by position as relational data

June 19, 2015 Leave a comment

Here’s a new post in the category “How to deal with that unfriendly design?”
Again, since the title is not very explicit, an example will surely explain it all :


This is the result we want out of that data :

----- ------- ----------  


Read more…

Categories: HowTo, SQL, XQuery Tags: ,


April 12, 2015 4 comments

Here’s the first article of the JSON category, focusing on JSON_TABLE for unnesting nested JSON arrays.


0- Setup

We’ll first create a table to hold some JSON documents :

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

and some docs :

insert into tmp_json
values (1,
  {"attr1":1, "attr2":"string1", "attr3":[1,2,3]},
  {"attr1":2, "attr2":"string2", "attr3":null},
  {"attr1":3, "attr2":"string3", "attr3":[4,5]}

insert into tmp_json
values (2,
  {"attr1":1, "attr2":"string1", "attr3":[7,8]},
  {"attr1":2, "attr2":"string2", "attr3":null}


Read more…

Categories: JSON Tags: , ,

PL/SQL RPN calculator – version 2

November 24, 2014 4 comments

This post presents a new version of my PL/SQL RPN calculator.
It now includes :

  • a regex-free tokenizer
  • a recursive-descent parser to validate the input expression
  • an improved evaluator based on a compiled “type-aware” RPN expression

Program and additional objects available here :


Read more…

Categories: Fun, PL/SQL Tags: ,

XMLTransform Tips and Tricks

November 16, 2014 4 comments

No real new stuff there but as I’m a big user of XSLT transformation inside the database, I just thought I could share a few interesting (hidden) things I’ve encountered so far, that is :

  1. Using XSLT parameters with XMLTransform
  2. Using text output method with XMLTransform and XMLType.transform method


Read more…