[
https://issues.apache.org/jira/browse/DERBY-6825?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6825:
---------------------------------
Attachment: derby-6825-02-aa-vti.diff
Attaching derby-6825-02-aa-vti.diff. This patch adds a SimpleJsonVTI table
function to the derby optional tools jar file and to the public api. Some
helper functions are added too.
When the simpleJson optional tool is loaded, it now creates the following
additional helper functions in the current schema. These functions are
described in the public api javadoc for
org.apache.derby.optional.api.SimpleJsonUtils:
1) readArrayFromFile() - This function reads a JSON document from a file and
turns the contents into a JSONArray udt. Here is its DDL:
{noformat}
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'
{noformat}
2) readArrayFromURL() - This functions reads a JSON document from an URL and
turns the contents into a JSONArray udt. Here is its DDL:
{noformat}
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'
{noformat}
3) readArrayFromString() - This function turns a JSON document string into a
JSONArray udt. Here is its DDL:
{noformat}
create function readArrayFromString( document varchar( 32672 ) )
returns JSONArray
language java parameter style java contains sql
external name
'org.apache.derby.optional.api.SimpleJsonUtils.readArrayFromString'
{noformat}
The SimpleJsonVTI takes a JSONArray udt as input and converts the udt into a
relational table. Here's the public api header comment for
org.apache.derby.optional.api.SimpleJsonVTI, explaining how to use this VTI in
combination with the helper functions added by the simpleJson optional tool:
{noformat}
/**
* <p>
* This is a table function which turns a JSON array into a relational
* ResultSet. This table function relies on the JSON.simple JSONArray class
* found at https://code.google.com/p/json-simple/.
* Each object in the array is turned into a row.
* The shape of the row is declared by the CREATE FUNCTION ddl
* and the shape corresponds to the key names found in the row objects.
* Provided that the values in those objects have the expected type,
* the following ResultSet accessors can be called:
* </p>
*
* <ul>
* <li>getString()</li>
* <li>getBoolean()</li>
* <li>getByte()</li>
* <li>getShort()</li>
* <li>getInt()</li>
* <li>getLong()</li>
* <li>getFloat()</li>
* <li>getDouble()</li>
* <li>getObject()</li>
* <li>getBigDecimal()</li>
* </ul>
*
* <p>
* This table function relies on the JSONArray type loaded by the simpleJson
optional
* tool. This table function can be combined with other JSONArray-creating
* functions provided by that tool.
* </p>
*
* <p>
* Here's an example of how to use this VTI on a JSON document read across
* the network using the readArrayFromURL function provided by the simpleJson
tool:
* </p>
*
* <pre>
* call syscs_util.syscs_register_tool( 'simpleJson', true );
*
* 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';
*
* select * from table
* (
* thermostatReadings
* (
* readArrayFromURL( 'https://thermostat.feed.org', 'UTF-8' )
* )
* ) t;
* </pre>
*
* <p>
* That returns a table like this:
* </p>
*
* <pre>
* id |temperature |fanOn
* ------------------------------------------
* 1 |70.3 |true
* 2 |65.5 |false
* </pre>
*
* <p>
* Here's an example of how to use this VTI on a JSON document string
* with the assistance of the readArrayFromString function provided by the
simpleJson tool:
* </p>
*
* <pre>
* select * from table
* (
* thermostatReadings
* (
* readArrayFromString
* (
* '[ { "id": 1, "temperature": 70.3, "fanOn": true }, { "id": 2,
"temperature": 65.5, "fanOn": false } ]'
* )
* )
* ) t;
* </pre>
*/
{noformat}
I am running tests now.
Touches the following files:
------------------------------------
M
java/build/org/apache/derbyBuild/lastgoodjarcontents/insane.derbyoptionaltools.jar.lastcontents
M
java/build/org/apache/derbyBuild/lastgoodjarcontents/sane.derbyoptionaltools.jar.lastcontents
M build.xml
M tools/javadoc/publishedapi.ant
Build machinery to add the VTI to the derbyoptionaltool.jar file and to the
public api.
M java/optional/org/apache/derby/optional/json/SimpleJsonTool.java
M java/optional/org/apache/derby/optional/api/SimpleJsonUtils.java
Makes the simpleJson tool load (and unload) the new helper functions.
------------------------------------
A java/optional/org/apache/derby/optional/api/SimpleJsonVTI.java
The new VTI which turns a JSON document into a relational table.
------------------------------------
A
java/testing/org/apache/derbyTesting/functionTests/tests/lang/thermostatReadings.dat
M
java/testing/org/apache/derbyTesting/functionTests/tests/lang/SimpleJsonTest.java
M
java/testing/org/apache/derbyTesting/functionTests/util/derby_tests.policy
M
java/testing/org/apache/derbyTesting/functionTests/util/PrivilegedFileOpsForTests.java
New tests for the helper functions and the VTI.
> Add basic JSON support to Derby.
> --------------------------------
>
> Key: DERBY-6825
> URL: https://issues.apache.org/jira/browse/DERBY-6825
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.12.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Attachments: SimpleJsonVTI.java, derby-6825-01-aa-optionalTool.diff,
> derby-6825-01-ab-optionalTool.diff, derby-6825-02-aa-vti.diff
>
>
> Other database are adding support for the popular JSON data interchange
> format defined by https://www.ietf.org/rfc/rfc4627.txt. We could add a VTI to
> Derby which would unpack a JSON document into a set of rows. We could also
> add a reverse transformation, which turns a query result into a JSON document.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
