JSON Update for the Impatient

July 30, 2015 Leave a 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 : http://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6255

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 : json_util.zip

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 :

<root>
  <ids>
    <id>7369</id>
    <id>7499</id>
    <id>7521</id>
  </ids>
  <names>
    <name>SMITH</name>
    <name>ALLEN</name>
    <name>WARD</name>
  </names>
  <jobs>
    <job>CLERK</job>
    <job>SALESMAN</job>
    <job>SALESMAN</job>
  </jobs>
</root>

This is the result we want out of that data :

   ID NAME    JOB
----- ------- ----------  
 7369 SMITH   CLERK
 7499 ALLEN   SALESMAN
 7521 WARD    SALESMAN

 

Read more…

Categories: HowTo, SQL, XQuery Tags: ,

JSON_TABLE Chaining

April 12, 2015 3 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,
'{"id":1,
 "myArray":[
  {"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,
'{"id":2,
 "myArray":[
  {"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 3 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 : rpn_util_v2.zip

 

Read more…

Categories: Fun, PL/SQL Tags: ,

XMLTransform Tips and Tricks

November 16, 2014 3 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…

How To : Parse sibling repeating groups without wrapper element

November 9, 2014 Leave a comment

Long time no post, so here’s a new (short) article.
Behind the cryptic title lies a situation like this :

<root>
  <id>1</id>
  <name>SMITH</name>
  <id>2</id>
  <name>WILSON</name>
  <id>3</id>
  <name>GRANT</name>
</root>

where each logical record {id, name} is not enclosed by a row tag.
This is the result we want out of that data :

ID    NAME
----- ---------
    1 SMITH
    2 WILSON
    3 GRANT

 

Read more…

XMLTABLE vs. External XSLT preprocessor

September 16, 2013 2 comments

We all know – I hope ;) – about XMLTABLE function to shred an XML document into relational rows and columns. In this article, I will describe an alternative approach to achieve the same result with an external table and the preprocessor feature.
And in case you wonder, no, it’s not one of this attempt to implement an XML parser using SQL*Loader syntax, something we can see from time to time as an answer to “How can I load my XML file using SQL*Loader?”.
This method relies on the external table preprocessor to transform the input XML file into CSV format and pipe the result to the loader.

 

Read more…