Authors
Forward Xu, Jark Wu
Status
Current state: "Under Discussion"
Discussion thread: http://apache-flink-mailing-list-archive.1008284.n3.nabble.com/DISCUSS-Support-JSON-functions-in-Flink-SQL-td32674.html
JIRA: here (<- link to https://issues.apache.org/jira/browse/FLINK-XXXX)
Released: <Flink Version>
...
Page properties | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Please keep the discussion on the mailing list rather than commenting on the wiki (wiki discussions get unwieldy fast).
Motivation
Currently, Flink SQL does not support the SQL 2016 JSON function. and many of the message middleware data are in JSON format. For example: Kafka, Flink SQL can support JSON operation to more easily parse the data in Kafka. In addition, the data in some relational databases is also stored and ingested in JSON format.
...
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 | 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.
The ARRAY WRAPPER clause defines how to represent a JSON array result in JSON_QUERY function. The following examples compare the wrapper behaviors.
Use in TableApi:
jsonExists
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
STRING.jsonExists(path)(Expression path [,JsonExistsBehavior behavior]) | Whether Whether a jsonValue satisfies a search criterion described using JSON path expression path.Table API currently only support path string parameter, doesn't support ON ERROR clause.. The JsonExistsBehavior is built as follows: behavior = JsonExistsBehavior.TrueOnError ("abc"). | BOOLEAN |
jsonValue
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
STRING.jsonValue(Expression path[,JsonValueBehavior behavior]) | Extract an SQL scalar from a jsonValue using JSON path expression path.Table API currently only support path string parameter, doesn't support ON ERROR clause. 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. |
jsonQuery
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.Table API currently only support path string parameter, doesn't support ON ERROR clause. The JsonQueryBehavior is built as follows: behavior = JsonQueryBehavior.withoutArray().defaultOnEmpty ("abc"). NullOnError (). | STRING |
Example Data:
{"a": "[1,2]", "b": [1,2], "c": "hi"} |
...
Use in SQL:
SQL SYNTAX | DESCRIPTION | RETURN TYPE |
JSON_OBJECT( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct JSON object using a series of key (name) value (value) pairs. | STRING |
JSON_OBJECTAGG( { [ KEY ] name VALUE value [ FORMAT JSON ] | name : value [ FORMAT JSON ] } [ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct a JSON object using a key (name) value (value) pair. | STRING |
JSON_ARRAY( { value [ FORMAT JSON ] } * [ { NULL | ABSENT } ON NULL ] ) | Construct a JSON array using a series of values (value). | STRING |
JSON_ARRAYAGG( value [ FORMAT JSON ] [ ORDER BY orderItem [, orderItem ]* ] [ { NULL | ABSENT } ON NULL ] ) | Aggregate function to construct a JSON array using a value (value). | STRING |
Use in TableApi:
jsonObject
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.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 |
jsonObjectAgg
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.Table API currently only supports JSON string operations..This means NULL ON NULL. | STRING |
jsonObjectAgg(ANY1,ANY2[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
jsonArray
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
jsonArray(ANY1, ANY2, ...[,SqlJsonConstructorNullClause.NULL_ON_NULL]) | Construct a JSON array using a series of values (value). Table API currently only supports JSON string operations.. This means NULL ON NULL. | STRING |
jsonArray(ANY1, ANY2, ...[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
jsonArrayAgg
JAVA/PYTHON/SCALA | DESCRIPTION | RETURN TYPE |
jsonArrayAgg(ANY[,SqlJsonConstructorNullClause.NULL_ON_NULL]jsonArrayAgg(ANY) | Aggregate function to construct a JSON array using a value (value). Table API currently only supports JSON string operations.. This means NULL ON NULL. | STRING |
jsonArrayAgg(ANY[,SqlJsonConstructorNullClause.ABSENT_ON_NULL]) | This means ABSENT ON NULL. | STRING |
Note: The flag FORMAT JSON indicates the value is formatted as JSON character string. When FORMAT JSON is used, the value should be de-parse from JSON character string to a SQL structured value.
...
OPERATOR | RESULT |
JSON_OBJECT('id', 87, 'name', 'carrot') | {"id": 87, "name": "carrot"} |
JSON_ARRAY(1, "abc", NULL, TRUE) | [1, "abc", null, true] |
Simple Simple Data Table T1:
o_id | attribute | value |
2 | color | red |
2 | fabric | silk |
3 | color | green |
3 | shape | square |
...
Use in SQL:
SQL SYNTAX | DESCRIPTION | RETURN TYPE |
jsonValue IS JSON [ VALUE ] | Whether jsonValue is a JSON value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS NOT JSON [ VALUE ] | Whether jsonValue is not a JSON value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS JSON SCALAR | Whether jsonValue is a JSON scalar value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS NOT JSON SCALAR | Whether jsonValue is not a JSON scalar value.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS JSON OBJECT | Whether jsonValue is a JSON object.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS NOT JSON OBJECT | Whether jsonValue is not a JSON object.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS JSON ARRAY | Whether jsonValue is a JSON array.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
jsonValue IS NOT JSON ARRAY | Whether jsonValue is not a JSON array.Table API functions currently only support JSON strings as input parameters. | BOOLEAN |
Use in TableApi:
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); |
...