- 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 TypeMethodDescriptionvoidvoidclose()getBigDecimal(int columnIndex) booleangetBoolean(int columnIndex) bytegetByte(int columnIndex) doublegetDouble(int columnIndex) floatgetFloat(int columnIndex) intgetInt(int columnIndex) longgetLong(int columnIndex) getObject(int columnIndex) shortgetShort(int columnIndex) getString(int columnIndex) booleannext()static SimpleJsonVTIreadArray(org.json.simple.JSONArray array) Create a SimpleJsonVTI from a JSONArray object.booleanwasNull()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, updateTimestampMethods inherited from class java.lang.Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, waitMethods 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:
wasNullin interfaceResultSet- Overrides:
wasNullin classVTITemplate
-
getWarnings
- Specified by:
getWarningsin interfaceResultSet- Overrides:
getWarningsin classVTITemplate- Throws:
SQLException
-
clearWarnings
- Specified by:
clearWarningsin interfaceResultSet- Overrides:
clearWarningsin classVTITemplate- Throws:
SQLException
-
getString
- Specified by:
getStringin interfaceResultSet- Overrides:
getStringin classVTITemplate- Throws:
SQLException
-
getBoolean
- Specified by:
getBooleanin interfaceResultSet- Overrides:
getBooleanin classVTITemplate- Throws:
SQLException
-
getByte
- Specified by:
getBytein interfaceResultSet- Overrides:
getBytein classVTITemplate- Throws:
SQLException
-
getShort
- Specified by:
getShortin interfaceResultSet- Overrides:
getShortin classVTITemplate- Throws:
SQLException
-
getInt
- Specified by:
getIntin interfaceResultSet- Overrides:
getIntin classVTITemplate- Throws:
SQLException
-
getLong
- Specified by:
getLongin interfaceResultSet- Overrides:
getLongin classVTITemplate- Throws:
SQLException
-
getFloat
- Specified by:
getFloatin interfaceResultSet- Overrides:
getFloatin classVTITemplate- Throws:
SQLException
-
getDouble
- Specified by:
getDoublein interfaceResultSet- Overrides:
getDoublein classVTITemplate- Throws:
SQLException
-
getObject
- Specified by:
getObjectin interfaceResultSet- Overrides:
getObjectin classVTITemplate- Throws:
SQLException
-
getBigDecimal
- Specified by:
getBigDecimalin interfaceResultSet- Overrides:
getBigDecimalin classVTITemplate- Throws:
SQLException
-