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| Modifier and Type | Method and Description |
|---|---|
void |
clearWarnings() |
void |
close() |
java.math.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) |
java.lang.Object |
getObject(int columnIndex) |
short |
getShort(int columnIndex) |
java.lang.String |
getString(int columnIndex) |
java.sql.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, updateTimestamppublic static SimpleJsonVTI readArray(org.json.simple.JSONArray array) throws java.sql.SQLException
Create a SimpleJsonVTI from a JSONArray object.
java.sql.SQLExceptionpublic void close()
throws java.sql.SQLException
java.sql.SQLExceptionpublic boolean next()
throws java.sql.SQLException
java.sql.SQLExceptionpublic boolean wasNull()
wasNull in interface java.sql.ResultSetwasNull in class VTITemplatepublic java.sql.SQLWarning getWarnings()
throws java.sql.SQLException
getWarnings in interface java.sql.ResultSetgetWarnings in class VTITemplatejava.sql.SQLExceptionpublic void clearWarnings()
throws java.sql.SQLException
clearWarnings in interface java.sql.ResultSetclearWarnings in class VTITemplatejava.sql.SQLExceptionpublic java.lang.String getString(int columnIndex)
throws java.sql.SQLException
getString in interface java.sql.ResultSetgetString in class VTITemplatejava.sql.SQLExceptionpublic boolean getBoolean(int columnIndex)
throws java.sql.SQLException
getBoolean in interface java.sql.ResultSetgetBoolean in class VTITemplatejava.sql.SQLExceptionpublic byte getByte(int columnIndex)
throws java.sql.SQLException
getByte in interface java.sql.ResultSetgetByte in class VTITemplatejava.sql.SQLExceptionpublic short getShort(int columnIndex)
throws java.sql.SQLException
getShort in interface java.sql.ResultSetgetShort in class VTITemplatejava.sql.SQLExceptionpublic int getInt(int columnIndex)
throws java.sql.SQLException
getInt in interface java.sql.ResultSetgetInt in class VTITemplatejava.sql.SQLExceptionpublic long getLong(int columnIndex)
throws java.sql.SQLException
getLong in interface java.sql.ResultSetgetLong in class VTITemplatejava.sql.SQLExceptionpublic float getFloat(int columnIndex)
throws java.sql.SQLException
getFloat in interface java.sql.ResultSetgetFloat in class VTITemplatejava.sql.SQLExceptionpublic double getDouble(int columnIndex)
throws java.sql.SQLException
getDouble in interface java.sql.ResultSetgetDouble in class VTITemplatejava.sql.SQLExceptionpublic java.lang.Object getObject(int columnIndex)
throws java.sql.SQLException
getObject in interface java.sql.ResultSetgetObject in class VTITemplatejava.sql.SQLExceptionpublic java.math.BigDecimal getBigDecimal(int columnIndex)
throws java.sql.SQLException
getBigDecimal in interface java.sql.ResultSetgetBigDecimal in class VTITemplatejava.sql.SQLExceptionApache Derby 10.13 API Documentation - Copyright © 2004,2016 The Apache Software Foundation. All Rights Reserved.