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.ColumnDescriptor
CLOSE_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, updateTimestamp
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
updateObject, updateObject, updateObject, updateObject
public static SimpleJsonVTI readArray(org.json.simple.JSONArray array) throws SQLException
Create a SimpleJsonVTI from a JSONArray object.
SQLException
public void close() throws SQLException
SQLException
public boolean next() throws SQLException
SQLException
public boolean wasNull()
wasNull
in interface ResultSet
wasNull
in class VTITemplate
public SQLWarning getWarnings() throws SQLException
getWarnings
in interface ResultSet
getWarnings
in class VTITemplate
SQLException
public void clearWarnings() throws SQLException
clearWarnings
in interface ResultSet
clearWarnings
in class VTITemplate
SQLException
public String getString(int columnIndex) throws SQLException
getString
in interface ResultSet
getString
in class VTITemplate
SQLException
public boolean getBoolean(int columnIndex) throws SQLException
getBoolean
in interface ResultSet
getBoolean
in class VTITemplate
SQLException
public byte getByte(int columnIndex) throws SQLException
getByte
in interface ResultSet
getByte
in class VTITemplate
SQLException
public short getShort(int columnIndex) throws SQLException
getShort
in interface ResultSet
getShort
in class VTITemplate
SQLException
public int getInt(int columnIndex) throws SQLException
getInt
in interface ResultSet
getInt
in class VTITemplate
SQLException
public long getLong(int columnIndex) throws SQLException
getLong
in interface ResultSet
getLong
in class VTITemplate
SQLException
public float getFloat(int columnIndex) throws SQLException
getFloat
in interface ResultSet
getFloat
in class VTITemplate
SQLException
public double getDouble(int columnIndex) throws SQLException
getDouble
in interface ResultSet
getDouble
in class VTITemplate
SQLException
public Object getObject(int columnIndex) throws SQLException
getObject
in interface ResultSet
getObject
in class VTITemplate
SQLException
public BigDecimal getBigDecimal(int columnIndex) throws SQLException
getBigDecimal
in interface ResultSet
getBigDecimal
in class VTITemplate
SQLException
Apache Derby 10.13 API Documentation - Copyright © 2004,2016 The Apache Software Foundation. All Rights Reserved.