[
https://issues.apache.org/jira/browse/DERBY-6825?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Rick Hillegas updated DERBY-6825:
---------------------------------
Attachment: SimpleJsonVTI.java
Attaching SimpleJsonVTI.java. This class allows you to turn a json document
string into a set of rows. The json document must be an array. The cells in the
array must be json objects. Each object is treated as a separate tuple.
SimpleJsonVTI relies on the popular and light-weight json-simple library found
here: https://code.google.com/p/json-simple/. The json-simple code is used by
several other Apache projects and it is licensed under the Apache 2.0 license.
Here's the header comment on SimpleJsonVTI:
/**
* <p>
* A table function which turns a JSON array into a relational
* ResultSet. Each object in the array is turned into a row.
* The shape of the row is declared by the CREATE FUNCTION ddl
* and 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>
* Here's an example of how to use this VTI on a JSON document string:
* </p>
*
* <pre>
* create function thermostatReadings( jsonDocument varchar( 32672 ) )
* returns table
* (
* "id" int,
* "temperature" float,
* "fanOn" boolean
* )
* language java parameter style derby_jdbc_result_set contains sql
* external name 'SimpleJsonVTI.fromString';
*
* select * from table
* (
* thermostatReadings
* (
* '[ { "id": 1, "temperature": 70.3, "fanOn": true }, { "id": 2,
"temperature": 65.5, "fanOn": false } ]'
* )
* ) t;
* </pre>
*
* <p>
* That returns this table:
* </p>
*
* <pre>
* id |temperature |fanOn
* ------------------------------------------
* 1 |70.3 |true
* 2 |65.5 |false
* </pre>
*/
If the community is interested, I could work on productizing SimpleJsonVTI.
This would involve the following:
1) Moving it into org.apache.derby.vti.
2) Adding unit tests.
3) Adding it to the public API.
4) Documenting it in the Developer's Guide. Note that in order to use the VTI,
users would need to add json-simple to the classpath.
5) Adding the json-simple jar file to the Derby codeline.
What do people think?
Thanks,
-Rick
> Add a VTI for turning JSON documents into relational ResultSets.
> ----------------------------------------------------------------
>
> 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
> Attachments: SimpleJsonVTI.java
>
>
> 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.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
