Using the simpleJson optional tool

The simpleJson optional tool creates functions and a user-defined type, which can be used to integrate relational data with data represented in the popular JSON format.

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:

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:

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 )
Related reference
Using the databaseMetaData optional tool
Using the foreignViews optional tool
Using the luceneSupport optional tool
Using the rawDBReader optional tool