Home > JSON > JSON Update for the Impatient

JSON Update for the Impatient

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

Also note that, since the whole JSON object is materialized in memory (much like a DOM object), this approach is not suitable for processing very large JSON content.

 

1) Installation

 

– Download the zip file.
– Extract the content of the zip in a directory of your choice, for example “c:\dev\workspace\JSON”.
– Load the JAR file into the database using the loadjava utility :

C:\Users\Marc>cd c:\dev\workspace\JSON

c:\dev\workspace\JSON>loadjava -u dev@ora11g -r -v -jarsasdbobjects -fileout result.log bin\json-update.jar

Password:
****
Classes Loaded: 20
Resources Loaded: 1
Sources Loaded: 0
Jars Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

The details of the loaded classes is in the result.log file.

– Create the PL/SQL package :

c:\dev\workspace\JSON>sqlplus dev@ora11g

SQL*Plus: Release 11.2.0.4.0 Production on Dim. Juil. 19 17:07:26 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @plsql\json_util.pck

Package created.


Package body created.

SQL> sho err
No errors.

 

2) API summary

 

Context handling methods

newContext
function newContext(
  p_content in clob
)
return ctxHandle;
Create a new context from a JSON object stored in a CLOB.
The JSON text is parsed into a org.json.JSONObject instance which is saved into a stored context.
closeContext
procedure closeContext(
  p_ctx in ctxHandle
);
Close a context opened using newContext() function.
getJSON
function getJSON(
  p_ctx in ctxHandle
) 
return clob;
Return the current JSON object associated with the context, as a CLOB.

 

Update methods

The following procedures update each item targeted by the path expression with a single value of the corresponding data type.
Nothing happens if the path expression targets no item, and no exception is raised.

setBoolean
procedure setBoolean(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in binary_integer
);
Set a boolean value (0=false, 1=true).
setNumber
procedure setNumber(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in number
);
Set a number value.
setNull
procedure setNull(
  p_ctx in ctxHandle
, p_path in varchar2
);
Set a JSON null value.
setString
procedure setString(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in varchar2
);
Set a string value.
setObject
procedure setObject(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in clob
);
Set a JSON object value.
setArray
procedure setArray(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in clob
);
Set a JSON array value.

 

Insert methods

The following procedures insert a single value of the corresponding data type into each item targeted by the path expression.

If the name parameter is not null, the procedure inserts a new name/value pair (if one does not already exist) into each JSON object targeted by the path expression.
Nothing happens if the path does not target any JSON object.

If the name parameter is null, the procedure inserts the value at each array index targeted by the path expression.
Inserts performed this way follow a “snapshot model” i.e. the value is inserted at the position(s) targeted by the path expression in the original configuration of the array.
Nothing happens if the path does not target any JSON array item.

insertBoolean
procedure insertBoolean(
  p_ctx in ctxHandle
, p_path in varchar2
, p_name in varchar2
, p_value in binary_integer
);
Insert a boolean value (0=false, 1=true).
insertNumber
procedure insertNumber(
  p_ctx in ctxHandle
, p_path in varchar2
, p_name in varchar2
, p_value in number
);
Insert a number value.
insertNull
procedure insertNull(
  p_ctx in ctxHandle
, p_path in varchar2
, p_name in varchar2
);
Insert a JSON null value.
insertString
procedure insertString(
  p_ctx in ctxHandle
, p_path in varchar2
, p_name in varchar2
, p_value in varchar2
);
Insert a string value.
insertObject
procedure insertObject(
  p_ctx in ctxHandle
, p_path in varchar2
, p_name in varchar2
, p_value in clob);
Insert a JSON object value.
insertArray
procedure insertArray(
  p_ctx in ctxHandle
, p_path in varchar2
, p_name in varchar2
, p_value in clob);
Insert a JSON array value.

 

Delete method

The following procedure delete all JSON items targeted by the path expression.

If the path targets a JSON object property then the whole name-value pair is removed from the object.
If the path targets a JSON array item then that item is removed from the array.
Nothing happens if the path does not target any item.

deleteJSON
procedure deleteJSON(
  p_ctx in ctxHandle
, p_path in varchar2
);
Delete one or more JSON items (object properties or array items).

 

Append methods

The following procedures append a single value of the corresponding data type to each JSON array targeted by the path expression.
Nothing happens if the path expression targets no array item, and no exception is raised.

appendBoolean
procedure appendBoolean(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in binary_integer
);
Append a boolean value (0=false, 1=true).
appendNumber
procedure appendNumber(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in number
);
Append a number value.
appendNull
procedure appendNull(
  p_ctx in ctxHandle
, p_path in varchar2
);
Append a JSON null value
appendString
procedure appendString(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in varchar2
);
Append a string value.
appendObject
procedure appendObject(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in clob
);
Append a JSON object value.
appendArray
procedure appendArray(
  p_ctx in ctxHandle
, p_path in varchar2
, p_value in clob
);
Append a JSON array value.

 

Those updating procedures are complemented by a SQL interface implemented as a set of functions.
Each function expects the same arguments as its procedure counterpart, except for the context handle which is replaced by a CLOB containing the JSON object to modify. The function returns the modified JSON object, as a CLOB too.
E.g.

setBoolean
function setBoolean(
  p_object in clob
, p_path in varchar2
, p_value in binary_integer
)
return clob;
Set a boolean value (0=false, 1=true) and return the modified object.

 

3) Examples

 

We’ll first create a table, and a JSON document :

create table json_messages (
  id      integer
, message clob
);

insert into json_messages
values (1,
'{
 "str":"ABC",
 "arr":[{"flag":true},{"flag":false}],
 "obj":{
   "item1":{"s1":1,"s2":2},
   "item2":{"s1":1,"s2":2},
   "item3":[0,1,2,3,4,5,6,7,8,9,10]
 }
}'
);

 

Using the context-based methods

This allows to perform multiple update operations reusing the same JSON object for each call.

declare

  ctx  json_util.ctxHandle;
  doc  clob;
  
begin

  select message into doc from json_messages where id = 1;

  ctx := json_util.newContext(doc);

  json_util.setBoolean(ctx, '$.obj.item3[1]', 1);
  json_util.setNull(ctx, '$.obj.item3[2]');
  json_util.setNumber(ctx, '$.obj.item1.s2', 1234.567);
  json_util.setString(ctx, '$.str', 'New string');
  json_util.insertBoolean(ctx, '$.*', 'new', 0);
  json_util.insertNumber(ctx, '$.obj.item3[5]', null, 1.23);
  json_util.deleteJSON(ctx, '$.obj.item3[8 to 10]');
  json_util.appendObject(ctx, '$.arr', '{"flag":true}');

  doc := json_util.getJSON(ctx);

  json_util.closeContext(ctx);

  dbms_output.put_line(doc);

end;
/

Operations performed, in this order :

  • set the 2nd item of item3 array in property obj to boolean value true.
  • set the 3rd item of item3 array in property obj to null.
  • set the s2 property of item1 object in obj to numeric value 1234.567.
  • set the str property to string value 'New string'.
  • insert a new property of boolean value false in all top-level properties whose value is a JSON object.
  • insert the numeric value 1.23 at position 5 into the item3 array in obj.
  • remove items at positions 8 through 10 from the item3 array in obj.
  • append the JSON object {"flag":true} to the arr array.

Result (formatted) :

{
  "str":"New string",
  "arr":[{"flag":true},{"flag":false},{"flag":true}],
  "obj":{
    "item2":{"s2":2,"s1":1},
    "item3":[0,true,null,3,4,1.23,5,6,10],
    "item1":{"s2":1234.567,"s1":1},
    "new":false
  }
}

 

Using the SQL wrappers

select json_util.appendNumber(message, '$.*.*', 11) 
from json_messages 
where id = 1 ;

Result (formatted) : 
{
  "str":"ABC",
  "arr":[{"flag":true},{"flag":false}],
  "obj":{
    "item2":{"s2":2,"s1":1},
    "item3":[0,1,2,3,4,5,6,7,8,9,10,11],
    "item1":{"s2":2,"s1":1}
  }
}

 

select json_util.insertNull(message, '$.arr[1]', 'x') 
from json_messages 
where id = 1 ;

Result (formatted) : 
{
  "str":"ABC",
  "arr":[{"flag":true},{"flag":false,"x":null}],
  "obj":{
    "item2":{"s2":2,"s1":1},
    "item3":[0,1,2,3,4,5,6,7,8,9,10],
    "item1":{"s2":2,"s1":1}
  }
}

 

select json_util.setNull(message, '$.obj.item3[0, 4 to 8]') 
from json_messages 
where id = 1 ;

Result (formatted) : 
{
  "str":"ABC",
  "arr":[{"flag":true},{"flag":false}],
  "obj":{
    "item2":{"s2":2,"s1":1},
    "item3":[null,1,2,3,null,null,null,null,null,9,10],
    "item1":{"s2":2,"s1":1}
  }
}

 

select json_util.setString(message, '$.obj.item2.*', 'new') 
from json_messages 
where id = 1 ;

Result (formatted) : 
{
  "str":"ABC",
  "arr":[{"flag":true},{"flag":false}],
  "obj":{
    "item2":{"s2":"new","s1":"new"},
    "item3":[0,1,2,3,4,5,6,7,8,9,10],
    "item1":{"s2":2,"s1":1}
  }
}

 

Advertisements
Categories: JSON Tags: , , ,
  1. Patrick
    November 4, 2015 at 14:21

    Nice implementation !

    Anyway to have a simple reader with JSON Path Expression ?

    Eg: json_util.get_value(p_ctx => my_handle, p_path => ‘$.friends[0].name’)

  2. V
    August 10, 2016 at 16:45

    When I try to load this jar in 12c, getting following error:
    Error while creating jar bin/json-update.jar
    ORA-06550: line 1, column 89:
    PLS-00302: component ‘START_LOADING_JAR’ must be declared
    ORA-06550: line 1, column 72:
    PL/SQL: Statement ignored

    • August 13, 2016 at 11:26

      This works OK for me :
      c:\dev\oracle\json\json-update>loadjava -u dev@pdb1 -r -v -jarsasdbobjects -fileout result.log bin\json-update.jar

      Password:
      ****
      Classes Loaded: 20
      Resources Loaded: 0
      Sources Loaded: 0
      Jars Loaded: 1
      Published Interfaces: 0
      Classes generated: 0
      Classes skipped: 0
      Synonyms Created: 0
      Errors: 0

      Is the JVM installed on your database?

  3. Anonymous
    November 10, 2016 at 16:04

    Hi
    I am also facing the below error while loading
    ORA-06550: line 1, column 89:
    PLS-00302: component ‘START_LOADING_JAR’ must be declared
    ORA-06550: line 1, column 72:
    PL/SQL: Statement ignored

    identical: db/json/path/Tokenizer$StringIterator
    identical: db/json/path/Tokenizer
    identical: db/json/path/ParseException
    identical: db/json/path/PathStep$StepType
    identical: db/json/path/PathStep
    identical: db/json/path/TokenType
    identical: db/json/path/Token
    identical: db/json/path/PathExpr$StepIterator
    identical: db/json/path/PathExpr
    identical: db/json/path/ArrayIndex
    identical: db/json/path/Parser
    identical: org/json/JSONArray
    identical: org/json/JSONString
    identical: db/json/api/JSONImpl$StoredCtx
    identical: db/json/api/JSONImpl
    identical: db/json/update/JSONUpdate
    identical: org/json/JSONObject$Null
    identical: org/json/JSONObject
    identical: org/json/JSONException
    identical: org/json/JSONTokener
    Error while creating jar bin/json-update.jar
    ORA-06550: line 1, column 25:
    PLS-00302: component ‘FINISH_LOADING_JAR’ must be declared
    ORA-06550: line 1, column 8:
    PL/SQL: Statement ignored

    skipping : class db/json/path/Tokenizer$StringIterator
    skipping : class db/json/path/Tokenizer
    skipping : class db/json/path/ParseException
    skipping : class db/json/path/PathStep$StepType
    skipping : class db/json/path/PathStep
    skipping : class db/json/path/TokenType
    skipping : class db/json/path/Token
    skipping : class db/json/path/PathExpr$StepIterator
    skipping : class db/json/path/PathExpr
    skipping : class db/json/path/ArrayIndex
    skipping : class db/json/path/Parser
    skipping : class org/json/JSONArray
    skipping : class org/json/JSONString
    skipping : class db/json/api/JSONImpl$StoredCtx
    skipping : class db/json/api/JSONImpl
    skipping : class db/json/update/JSONUpdate
    skipping : class org/json/JSONObject$Null
    skipping : class org/json/JSONObject
    skipping : class org/json/JSONException
    skipping : class org/json/JSONTokener
    The following operations failed
    jar bin/json-update.jar: creation (createFailed)

    select comp_name, version, status from dba_registry WHERE comp_name like ‘%JAVA%’;

    JServer JAVA Virtual Machine 12.1.0.2.0 VALID

  4. Anonymous
    November 11, 2016 at 09:56

    How do we use this to update multiple attributes with a single update statement

    • November 11, 2016 at 14:49

      It’s not supported. However, you can create a function that’ll perform the necessary updates using the PL/SQL interface (using the same context) and return the modified object.

  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: