twalthr commented on a change in pull request #16858:
URL: https://github.com/apache/flink/pull/16858#discussion_r701818138
##########
File path: docs/data/sql_functions.yml
##########
@@ -667,6 +666,46 @@ json:
JSON_VALUE('{"a": true}', 'strict $.b'
DEFAULT FALSE ON ERROR)
```
+ - sql: JSON_QUERY(jsonValue, path [ { WITHOUT | WITH CONDITIONAL | WITH
UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT |
ERROR } ON EMPTY ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ])
+ table: STRING.jsonQuery(path [, JsonQueryWrapper [,
JsonQueryOnEmptyOrError, JsonQueryOnEmptyOrError ] ])
+ description: |
+ Extracts JSON values from a JSON string.
+
+ The result is always returned as a `STRING`. The `RETURNING` clause is
currently not supported.
+
+ The `wrappingBehavior` determines whether the extracted value should be
wrapped into an array,
+ and whether to do so unconditionally or only if the value itself isn't
an array already.
+
+ `onEmpty` and `onError` determine the behavior in case the path
expression is empty, or in
+ case an error was raised, respectively. By default, in both cases `null`
is returned. Other
+ choices are to use an empty array, an empty object, or to raise an error.
+
+ ```
+ -- '{ "b": 1 }'
+ JSON_QUERY('{ "a": { "b": 1 } }', '$.a')
+ -- '[1, 2]'
+ JSON_QUERY('[1, 2]', '$')
+
+ -- Wrap result into an array
+ -- '[{}]'
+ JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER)
+ -- '[1, 2]'
+ JSON_QUERY('[1, 2]', '$' WITH CONDITIONAL ARRAY WRAPPER)
+ -- '[[1, 2]]'
+ JSON_QUERY('[1, 2]', '$' WITH UNCONDITIONAL ARRAY WRAPPER)
+
+ -- Scalars must be wrapped to be returned
+ -- NULL
+ JSON_QUERY(1, '$')
+ -- '[1]'
+ JSON_QUERY(1, '$' WITH CONDITIONAL ARRAY WRAPPER)
+
+ -- Behavior if path expression is empty / there is an error
+ -- '{}'
+ JSON_QUERY('{}', 'lax $.invalid' EMPTY OBJECT ON EMPTY)
+ -- '[]'
+ JSON_QUERY('{}', 'strict $.invalid' EMPTY ARRAY ON ERROR)
Review comment:
and as always: add an example in case the input is NULL 😅
##########
File path: flink-python/pyflink/table/expression.py
##########
@@ -1511,6 +1546,51 @@ def json_value(self,
on_error._to_j_json_value_on_empty_or_error(),
default_on_error)
+ def json_query(self, path: str, wrapping_behavior=JsonQueryWrapper.WITHOUT,
+ on_empty=JsonQueryOnEmptyOrError.NULL,
+ on_error=JsonQueryOnEmptyOrError.NULL) -> 'Expression':
+ """
+ Extracts JSON values from a JSON string.
+
+ This follows the ISO/IEC TR 19075-6 specification for JSON support in
SQL.
+
+ The result is always returned as a `STRING`. The `RETURNING` clause is
currently not
Review comment:
nit: maybe drop `The `RETURNING` clause is currently not supported.` for
Table API users because there is no "clause"?
##########
File path:
flink-table/flink-table-planner/src/main/scala/org/apache/flink/table/planner/codegen/calls/BuiltInMethods.scala
##########
@@ -24,12 +24,14 @@ import org.apache.flink.table.runtime.functions._
import org.apache.calcite.avatica.util.{DateTimeUtils, TimeUnitRange}
import org.apache.calcite.linq4j.tree.Types
import org.apache.calcite.runtime.{JsonFunctions, SqlFunctions}
-import org.apache.calcite.sql.{SqlJsonExistsErrorBehavior,
SqlJsonValueEmptyOrErrorBehavior}
+import org.apache.calcite.sql.{SqlJsonExistsErrorBehavior,
SqlJsonQueryEmptyOrErrorBehavior,
+ SqlJsonQueryWrapperBehavior, SqlJsonValueEmptyOrErrorBehavior}
import java.lang.reflect.Method
import java.lang.{Byte => JByte, Integer => JInteger, Long => JLong, Short =>
JShort}
import java.util.TimeZone
+
Review comment:
undo?
##########
File path:
flink-table/flink-table-planner/src/test/java/org/apache/flink/table/planner/functions/JsonFunctionsITCase.java
##########
@@ -313,4 +321,121 @@ private static TestSpec jsonValue() throws Exception {
true,
DataTypes.BOOLEAN().notNull()));
}
+
+ private static List<TestSpec> jsonQuery() throws Exception {
+ final InputStream jsonResource =
Review comment:
nit: move this into a helper method for reading a string, other tests
use the same lines too?
##########
File path: docs/data/sql_functions.yml
##########
@@ -667,6 +666,46 @@ json:
JSON_VALUE('{"a": true}', 'strict $.b'
DEFAULT FALSE ON ERROR)
```
+ - sql: JSON_QUERY(jsonValue, path [ { WITHOUT | WITH CONDITIONAL | WITH
UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT |
ERROR } ON EMPTY ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ])
+ table: STRING.jsonQuery(path [, JsonQueryWrapper [,
JsonQueryOnEmptyOrError, JsonQueryOnEmptyOrError ] ])
+ description: |
+ Extracts JSON values from a JSON string.
+
+ The result is always returned as a `STRING`. The `RETURNING` clause is
currently not supported.
Review comment:
why not? a Calcite issue?
##########
File path: flink-python/pyflink/table/tests/test_expression.py
##########
@@ -207,6 +207,11 @@ def test_expression(self):
JsonValueOnEmptyOrError.DEFAULT, 42,
JsonValueOnEmptyOrError.ERROR, None)))
+ self.assertEqual("JSON_QUERY('{}', '$.x', WITHOUT, NULL, EMPTY_ARRAY)",
Review comment:
`WITHOUT_ARRAY` or `WITHOUT_WRAPPER` would read nicer
##########
File path: flink-python/pyflink/table/tests/test_expression.py
##########
@@ -207,6 +207,11 @@ def test_expression(self):
JsonValueOnEmptyOrError.DEFAULT, 42,
JsonValueOnEmptyOrError.ERROR, None)))
+ self.assertEqual("JSON_QUERY('{}', '$.x', WITHOUT, NULL, EMPTY_ARRAY)",
Review comment:
I just noticed that Calcite has also named it like that, that supports
my comment 😅
##########
File path: docs/data/sql_functions_zh.yml
##########
@@ -799,6 +798,46 @@ json:
JSON_VALUE('{"a": true}', 'strict $.b'
DEFAULT FALSE ON ERROR)
```
+ - sql: JSON_QUERY(jsonValue, path [ { WITHOUT | WITH CONDITIONAL | WITH
UNCONDITIONAL } [ ARRAY ] WRAPPER ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT |
ERROR } ON EMPTY ] [ { NULL | EMPTY ARRAY | EMPTY OBJECT | ERROR } ON ERROR ])
+ table: STRING.jsonQuery(path [, JsonQueryWrapper [,
JsonQueryOnEmptyOrError, JsonQueryOnEmptyOrError ] ])
+ description: |
+ Extracts JSON values from a JSON string.
+
+ The result is always returned as a `STRING`. The `RETURNING` clause is
currently not supported.
+
+ The `wrappingBehavior` determines whether the extracted value should be
wrapped into an array,
+ and whether to do so unconditionally or only if the value itself isn't
an array already.
+
+ `onEmpty` and `onError` determine the behavior in case the path
expression is empty, or in
+ case an error was raised, respectively. By default, in both cases `null`
is returned. Other
+ choices are to use an empty array, an empty object, or to raise an error.
+
+ ```
+ -- '{ "b": 1 }'
+ JSON_QUERY('{ "a": { "b": 1 } }', '$.a')
+ -- '[1, 2]'
+ JSON_QUERY('[1, 2]', '$')
+
+ -- Wrap result into an array
+ -- '[{}]'
+ JSON_QUERY('{}', '$' WITH CONDITIONAL ARRAY WRAPPER)
Review comment:
what happens if drop `ARRAY` like `WITH CONDITIONAL WRAPPER`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
