- All Implemented Interfaces:
AutoCloseable
,ResultSet
,Wrapper
,AwareVTI
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:
- getString()
- getBoolean()
- getByte()
- getShort()
- getInt()
- getLong()
- getFloat()
- getDouble()
- getObject()
- getBigDecimal()
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;
-
Nested Class Summary
Nested classes/interfaces inherited from class org.apache.derby.vti.VTITemplate
VTITemplate.ColumnDescriptor
-
Field Summary
Fields inherited from interface java.sql.ResultSet
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
-
Method Summary
Modifier and TypeMethodDescriptionvoid
void
close()
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) getObject
(int columnIndex) short
getShort
(int columnIndex) getString
(int columnIndex) boolean
next()
static SimpleJsonVTI
readArray
(org.json.simple.JSONArray array) Create a SimpleJsonVTI from a JSONArray object.boolean
wasNull()
Methods inherited from class org.apache.derby.vti.VTITemplate
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
Methods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
Methods inherited from interface java.sql.ResultSet
updateObject, updateObject, updateObject, updateObject
-
Method Details
-
readArray
Create a SimpleJsonVTI from a JSONArray object.
- Parameters:
array
- a json array- Returns:
- a VTI for reading the json array
- Throws:
SQLException
- on error
-
close
- Throws:
SQLException
-
next
- Throws:
SQLException
-
wasNull
public boolean wasNull()- Specified by:
wasNull
in interfaceResultSet
- Overrides:
wasNull
in classVTITemplate
-
getWarnings
- Specified by:
getWarnings
in interfaceResultSet
- Overrides:
getWarnings
in classVTITemplate
- Throws:
SQLException
-
clearWarnings
- Specified by:
clearWarnings
in interfaceResultSet
- Overrides:
clearWarnings
in classVTITemplate
- Throws:
SQLException
-
getString
- Specified by:
getString
in interfaceResultSet
- Overrides:
getString
in classVTITemplate
- Throws:
SQLException
-
getBoolean
- Specified by:
getBoolean
in interfaceResultSet
- Overrides:
getBoolean
in classVTITemplate
- Throws:
SQLException
-
getByte
- Specified by:
getByte
in interfaceResultSet
- Overrides:
getByte
in classVTITemplate
- Throws:
SQLException
-
getShort
- Specified by:
getShort
in interfaceResultSet
- Overrides:
getShort
in classVTITemplate
- Throws:
SQLException
-
getInt
- Specified by:
getInt
in interfaceResultSet
- Overrides:
getInt
in classVTITemplate
- Throws:
SQLException
-
getLong
- Specified by:
getLong
in interfaceResultSet
- Overrides:
getLong
in classVTITemplate
- Throws:
SQLException
-
getFloat
- Specified by:
getFloat
in interfaceResultSet
- Overrides:
getFloat
in classVTITemplate
- Throws:
SQLException
-
getDouble
- Specified by:
getDouble
in interfaceResultSet
- Overrides:
getDouble
in classVTITemplate
- Throws:
SQLException
-
getObject
- Specified by:
getObject
in interfaceResultSet
- Overrides:
getObject
in classVTITemplate
- Throws:
SQLException
-
getBigDecimal
- Specified by:
getBigDecimal
in interfaceResultSet
- Overrides:
getBigDecimal
in classVTITemplate
- Throws:
SQLException
-