public class SimpleJsonVTI extends VTITemplate
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:
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.
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:
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;
That returns a table like this:
id |temperature |fanOn ------------------------------------------ 1 |70.3 |true 2 |65.5 |false
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:
select * from table
(
thermostatReadings
(
readArrayFromString
(
'[ { "id": 1, "temperature": 70.3, "fanOn": true }, { "id": 2, "temperature": 65.5, "fanOn": false } ]'
)
)
) t;
VTITemplate.ColumnDescriptorCLOSE_CURSORS_AT_COMMIT, CONCUR_READ_ONLY, CONCUR_UPDATABLE, FETCH_FORWARD, FETCH_REVERSE, FETCH_UNKNOWN, HOLD_CURSORS_OVER_COMMIT, TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, TYPE_SCROLL_SENSITIVE| Modifier and Type | Method and Description |
|---|---|
void |
clearWarnings() |
void |
close() |
BigDecimal |
getBigDecimal(int columnIndex) |
boolean |
getBoolean(int columnIndex) |
byte |
getByte(int columnIndex) |
double |
getDouble(int columnIndex) |
float |
getFloat(int columnIndex) |
int |
getInt(int columnIndex) |
long |
getLong(int columnIndex) |
Object |
getObject(int columnIndex) |
short |
getShort(int columnIndex) |
String |
getString(int columnIndex) |
SQLWarning |
getWarnings() |
boolean |
next() |
static SimpleJsonVTI |
readArray(org.json.simple.JSONArray array)
Create a SimpleJsonVTI from a JSONArray object.
|
boolean |
wasNull() |
absolute, afterLast, beforeFirst, cancelRowUpdates, deleteRow, findColumn, first, getArray, getArray, getAsciiStream, getAsciiStream, getBigDecimal, getBigDecimal, getBigDecimal, getBinaryStream, getBinaryStream, getBlob, getBlob, getBoolean, getByte, getBytes, getBytes, getCharacterStream, getCharacterStream, getClob, getClob, getConcurrency, getContext, getCursorName, getDate, getDate, getDate, getDate, getDouble, getFetchDirection, getFetchSize, getFloat, getHoldability, getInt, getLong, getMetaData, getNCharacterStream, getNCharacterStream, getNClob, getNClob, getNString, getNString, getObject, getObject, getObject, getObject, getObject, getRef, getRef, getReturnTableSignature, getRow, getRowId, getRowId, getShort, getSQLXML, getSQLXML, getStatement, getString, getTime, getTime, getTime, getTime, getTimestamp, getTimestamp, getTimestamp, getTimestamp, getType, getUnicodeStream, getUnicodeStream, getURL, getURL, insertRow, isAfterLast, isBeforeFirst, isClosed, isFirst, isLast, isWrapperFor, last, moveToCurrentRow, moveToInsertRow, previous, refreshRow, relative, rowDeleted, rowInserted, rowUpdated, setContext, setFetchDirection, setFetchSize, unwrap, updateArray, updateArray, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateAsciiStream, updateBigDecimal, updateBigDecimal, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBinaryStream, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBlob, updateBoolean, updateBoolean, updateByte, updateByte, updateBytes, updateBytes, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateCharacterStream, updateClob, updateClob, updateClob, updateClob, updateClob, updateClob, updateDate, updateDate, updateDouble, updateDouble, updateFloat, updateFloat, updateInt, updateInt, updateLong, updateLong, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNCharacterStream, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNClob, updateNString, updateNString, updateNull, updateNull, updateObject, updateObject, updateObject, updateObject, updateRef, updateRef, updateRow, updateRowId, updateRowId, updateShort, updateShort, updateSQLXML, updateSQLXML, updateString, updateString, updateTime, updateTime, updateTimestamp, updateTimestampequals, getClass, hashCode, notify, notifyAll, toString, wait, wait, waitupdateObject, updateObject, updateObject, updateObjectpublic static SimpleJsonVTI readArray(org.json.simple.JSONArray array) throws SQLException
Create a SimpleJsonVTI from a JSONArray object.
SQLExceptionpublic void close()
throws SQLException
SQLExceptionpublic boolean next()
throws SQLException
SQLExceptionpublic boolean wasNull()
wasNull in interface ResultSetwasNull in class VTITemplatepublic SQLWarning getWarnings() throws SQLException
getWarnings in interface ResultSetgetWarnings in class VTITemplateSQLExceptionpublic void clearWarnings()
throws SQLException
clearWarnings in interface ResultSetclearWarnings in class VTITemplateSQLExceptionpublic String getString(int columnIndex) throws SQLException
getString in interface ResultSetgetString in class VTITemplateSQLExceptionpublic boolean getBoolean(int columnIndex)
throws SQLException
getBoolean in interface ResultSetgetBoolean in class VTITemplateSQLExceptionpublic byte getByte(int columnIndex)
throws SQLException
getByte in interface ResultSetgetByte in class VTITemplateSQLExceptionpublic short getShort(int columnIndex)
throws SQLException
getShort in interface ResultSetgetShort in class VTITemplateSQLExceptionpublic int getInt(int columnIndex)
throws SQLException
getInt in interface ResultSetgetInt in class VTITemplateSQLExceptionpublic long getLong(int columnIndex)
throws SQLException
getLong in interface ResultSetgetLong in class VTITemplateSQLExceptionpublic float getFloat(int columnIndex)
throws SQLException
getFloat in interface ResultSetgetFloat in class VTITemplateSQLExceptionpublic double getDouble(int columnIndex)
throws SQLException
getDouble in interface ResultSetgetDouble in class VTITemplateSQLExceptionpublic Object getObject(int columnIndex) throws SQLException
getObject in interface ResultSetgetObject in class VTITemplateSQLExceptionpublic BigDecimal getBigDecimal(int columnIndex) throws SQLException
getBigDecimal in interface ResultSetgetBigDecimal in class VTITemplateSQLExceptionApache Derby 10.14 API Documentation - Copyright © 2004,2018 The Apache Software Foundation. All Rights Reserved.