Using the simpleJson optional tool

The simpleJson optional tool relies on support classes provided by the third party JSON.simple jar file. That jar file can be obtained from http://code.google.com/p/json-simple/. Before loading the simpleJson tool, make sure that your classpath contains this third party jar file as well as derby.jar and derbyoptionaltools.jar. The simpleJson tool has been tested with version 1.1 of JSON.simple (json_simple-1.1.jar).

You can load and unload the simpleJson tool by using the SYSCS_UTIL.SYSCS_REGISTER_TOOL system procedure. See the Derby Reference Manual for information about this procedure.

To load the simpleJson tool, use the following statement:

call syscs_util.syscs_register_tool( 'simpleJson', true )

This command creates a JSONArray user-defined type in the current schema. That type is bound to the JSON array abstraction provided by JSON.simple (org.json.simple.JSONArray). The registration command also creates the following functions in the current schema. Javadoc for these functions can be found in the public API for org.apache.derby.optional.api.SimpleJsonUtils:

• readArrayFromString() - This function turns a JSON document string into a JSONArray value.
• readArrayFromFile() - This function reads a JSON document stored in a file and turns that document into a JSONArray value.
• readArrayFromURL() - This function reads a JSON document from an URL and turns that document into a JSONArray value.
• arrayToClob() - This function turns a JSONArray value into a Clob so that it can be inserted into a text column.

These functions have the following signatures:

create function readArrayFromString( document varchar( 32672 ) )
returns JSONArray
language java parameter style java contains sql
external name 'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromString'

create function readArrayFromFile
( fileName varchar( 32672 ), characterSetName varchar( 100 ) )
returns JSONArray
language java parameter style java contains sql
external name 'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromFile'

create function readArrayFromURL
( urlString varchar( 32672 ), characterSetName varchar( 100 ) )
returns JSONArray
language java parameter style java contains sql
external name 'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromURL'

create function arrayToClob( jsonDocument JSONArray ) returns clob
language java parameter style java no sql
external name 'org.apache.derby.optional.api.SimpleJsonUtils.arrayToClob'

The first three functions can then be used to turn JSON documents into tabular data sets using org.apache.derby.optional.api.SimpleJsonVTI. That class is documented in Derby's public api too. Using this technique, you can join JSON data with other relational data. You can also exploit this technique to import JSON data into Derby tables. Here's an example of how to use these functions to import data:

create function thermostatReadings( jsonDocument JSONArray )
returns table
(
  "id" int,
  "temperature" float,
  "fanOn" boolean
)
language java parameter style derby_jdbc_result_set contains sql
external name 'org.apache.derby.optional.api.SimpleJsonVTI.readArray';

insert into thermostatReadings
 select * from table
 (
    thermostatReadings
    (
       readArrayFromURL( 'https://thermostat.feed.org', 'UTF-8' )
    )
 ) t;

After running that query, the contents of the target table might look something like this:

 id         |temperature             |fanOn
 ------------------------------------------
 1          |70.3                    |true 
 2          |65.5                    |false

The simpleJson tool declares one more function:

• toJSON() - This function turns a query result into a JSONArray value.

This function has the following signature:

create function toJSON
(
    queryString varchar( 32672 ),
    queryArgs varchar( 32672 ) ...
)
returns JSONArray
language java parameter style derby reads sql data
external name 'org.apache.derby.optional.json.SimpleJsonTool.toJSON

toJSON() prepares the query, plugs the optional queryArgs into the query's ? parameters, executes the query, and returns the results packed into a JSONArray. Here's an example of how to use this function:

values( toJSON( 'select * from thermostatReadings where "id" = ?', '1' ) );

That statement returns a JSONArray which looks something like this:

[
    { "id": 1, "temperature": 70.3, "fanOn": true }
]

The tool can be unloaded via the following command. This command drops the JSONArray type and all of the functions discussed above:

call syscs_util.syscs_register_tool( 'simpleJson', false )