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

- 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 )

Parent topic: Optional tools
Related reference
Using the databaseMetaData optional tool
Using the foreignViews optional tool
Using the luceneSupport optional tool
Using the rawDBReader optional tool