Archive for the ‘JSON’ Category

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…


JSON Flattening Part 1 – The JSONFlatten function

April 30, 2017 Leave a comment

This article presents an approach to flatten out a JSON structure into an “Edge” table.
That’s something I’ve been exploring multiple times in the past for XML data, see for example : Yet Another XML Flattening Technique.

I’ll apply the same technique used for XMLFlattenDOM function to JSON data, leveraging the new JSON PL/SQL types introduced in the 12.2 release : JSON_ELEMENT_T, JSON_OBJECT_T, JSON_ARRAY_T.
As those object types implement a DOM-like API (based on the internal JDOM_T object), it is quite easy to adapt my previous approach to JSON.

Given the following (very) simple JSON object :


This is the output we’re looking for :

--- ------ ---------- ------- ---- -----------
  1 object          0
  2 string          1 key               ABC
  3 array           1 arr
  4 number          3            1      1
  5 number          3            2      2
  6 number          3            3      3
  7 null            3            4


Read more…

Simple CSV parsing using XMLTABLE or JSON_TABLE

April 22, 2016 Leave a comment

Oracle does not currently provide any built-in functionality to parse a flat file stored in the database as CLOB (or BLOB), like the External Table facility for external files.
Mike Kutz submitted the idea a couple of years ago : parse CLOBS/BLOBS using “external table” capability.

Parsing a simple CSV content, with no quoted field, is however possible with minimum effort using XMLTABLE or JSON_TABLE functions.
Both approaches are based on a little preprocessing to convert the data to the required format and pass it to the corresponding function.

The last part of this post will focus on the limitations.

Read more…

Categories: JSON, SQL, XML DB Tags: , ,

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: , ,

JSON Update for the Impatient

July 30, 2015 8 comments

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: , , ,


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: , ,