THIS IS A TEST INSTANCE. ALL YOUR CHANGES WILL BE LOST!!!!
...
Query Functions
Use in SQL:
SQL SYNTAX | DESCRIPTION | RETURN TYPE |
JSON_EXISTS(jsonValue, path [ { TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ] ) | Whether a jsonValue satisfies a search criterion described using JSON path expression path. | BOOLEAN |
JSON_VALUE(jsonValue, path [ RETURNING type ] [ { ERROR | NULL | DEFAULT expr } ON EMPTY ] [ { ERROR | NULL | DEFAULT expr } ON ERROR ] ) | Extract an SQL scalar from a jsonValue using JSON path expression path. | if no "RETURNING type" clause, STRING is returned. If a "RETURNING type" clause is included, an attempt is made to cast to the specified type. For example: json_value ('{"foo": 100}', 'lax $ .foo' returning integer null on empty) will return an integer type. |
JSON_QUERY(jsonValue, path [ { WITHOUT [ ARRAY ] | WITH [ CONDITIONAL | UNCONDITIONAL ] [ ARRAY ] } WRAPPER ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON EMPTY ] [ { ERROR | NULL | EMPTY ARRAY | EMPTY OBJECT } ON ERROR ] ) | Extract a JSON object or JSON array from jsonValue using the path JSON path expression. | STRING |
Note: The ON ERROR and ON EMPTY clauses define the fallback behavior of the function when an error is thrown or a null value is about to be returned.
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
STRING.jsonExists((Expression path [,JsonExistsBehavior behavior]) | Whether a jsonValue satisfies a search criterion described using JSON path expression path. The JsonExistsBehavior is built as follows: behavior = JsonExistsBehavior.TrueOnError ("abc"). | BOOLEAN |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
STRING.jsonValue(Expression path[,JsonValueBehavior behavior]) | Extract an SQL scalar from a jsonValue using JSON path expression path. The JsonValueBehavior is built as follows: behavior = JsonValueBehavior.defaultOnEmpty ("abc"). NullOnError(). or behavior = JsonValueBehavior.returnInt().defaultOnEmpty ("abc"). NullOnError(). | if no "RETURNING type" clause, STRING is returned. If a "RETURNING type" clause is included, an attempt is made to cast to the specified type. For example: json_value ('{"foo": 100}', 'lax $ .foo' returning integer null on empty) will return an integer type. |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
STRING.jsonQuery(Expression path [,JsonQueryBehavior behavior]) | Extract a JSON object or JSON array from jsonValue using the path JSON path expression. The JsonQueryBehavior is built as follows: behavior = JsonQueryBehavior.withoutArray().defaultOnEmpty ("abc"). NullOnError (). | STRING |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
jsonObject(ANY1, ANY2, …[,SqlJsonConstructorNullClause.NULL_ON_NULL]) | Construct JSON object using a series of key (name) value (value) pairs.Table API currently only supports JSON string operations. This means NULL ON NULL. | STRING |
jsonObject(ANY1, ANY2, …[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
jsonObjectAgg(ANY1,ANY2[,SqlJsonConstructorNullClause.NULL_ON_NULL]) | Aggregate function to construct a JSON object using a key (name) value (value) pair.This means NULL ON NULL. | STRING |
jsonObjectAgg(ANY1,ANY2[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
jsonArray(ANY1, ANY2, ...[,SqlJsonConstructorNullClause.NULL_ON_NULL]) | Construct a JSON array using a series of values (value). This means NULL ON NULL. | STRING |
jsonArray(ANY1, ANY2, ...[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
jsonArrayAgg(ANY[,SqlJsonConstructorNullClause.NULL_ON_NULL]) | Aggregate function to construct a JSON array using a value (value). This means NULL ON NULL. | STRING |
jsonArrayAgg(ANY[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
...
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
ANY.isJsonValue() | Whether jsonValue is a JSON value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isNotJsonValue() | Whether jsonValue is not a JSON value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isJsonScalar() | Whether jsonValue is a JSON scalar value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isNotJsonScalar() | Whether jsonValue is not a JSON scalar value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isJsonObject() | Whether jsonValue is a JSON object.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isNotJsonObject() | Whether jsonValue is not a JSON object.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isJsonArray() | Whether jsonValue is a JSON array.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
ANY.isNotJsonArray() | Whether jsonValue is not a JSON array.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
The test example:
tester.checkBoolean("'{}' is json value", true); tester.checkBoolean("'{]' is json value", false); tester.checkBoolean("'{}' is json object", true); tester.checkBoolean("'[]' is json object", false); tester.checkBoolean("'{}' is json array", false); tester.checkBoolean("'[]' is json array", true); tester.checkBoolean("'100' is json scalar", true); tester.checkBoolean("'[]' is json scalar", false); tester.checkBoolean("'{}' is not json value", false); tester.checkBoolean("'{]' is not json value", true); tester.checkBoolean("'{}' is not json object", false); tester.checkBoolean("'[]' is not json object", true); tester.checkBoolean("'{}' is not json array", true); tester.checkBoolean("'[]' is not json array", false); tester.checkBoolean("'100' is not json scalar", false); tester.checkBoolean("'[]' is not json scalar", true); |
Implementation Details
Table Api interface proposal
...