This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new ea441e7c75 [CALCITE-6365] Support for RETURNING clause of JSON_QUERY
ea441e7c75 is described below
commit ea441e7c75433bd2354f355f69ca0f61d658ef55
Author: Dawid Wysakowicz <dwysakow...@apache.org>
AuthorDate: Wed Apr 24 14:08:29 2024 +0200
[CALCITE-6365] Support for RETURNING clause of JSON_QUERY
---
core/src/main/codegen/templates/Parser.jj | 7 +-
.../calcite/adapter/enumerable/RexImpTable.java | 30 ++++++-
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/JsonFunctions.java | 53 ++++++++----
.../calcite/sql/fun/SqlJsonQueryFunction.java | 95 ++++++++++++++++++++--
.../calcite/sql2rel/StandardConvertletTable.java | 44 ++++++++--
.../org/apache/calcite/util/BuiltInMethod.java | 3 +-
.../calcite/runtime/CalciteResource.properties | 1 +
.../java/org/apache/calcite/test/JdbcTest.java | 65 +++++++++++++++
.../apache/calcite/test/SqlJsonFunctionsTest.java | 35 +++++++-
.../org/apache/calcite/test/SqlValidatorTest.java | 8 ++
.../apache/calcite/sql/parser/SqlParserTest.java | 3 +
12 files changed, 308 insertions(+), 39 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 93fda1d8a0..e8c59ba374 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6706,7 +6706,7 @@ SqlNode JsonQueryWrapperBehavior() :
SqlCall JsonQueryFunctionCall() :
{
- final SqlNode[] args = new SqlNode[5];
+ final SqlNode[] args = new SqlNode[6];
SqlNode e;
List<SqlNode> commonSyntax;
final Span span;
@@ -6718,6 +6718,11 @@ SqlCall JsonQueryFunctionCall() :
args[0] = commonSyntax.get(0);
args[1] = commonSyntax.get(1);
}
+ [
+ e = JsonReturningClause() {
+ args[5] = e;
+ }
+ ]
[
e = JsonQueryWrapperBehavior() <WRAPPER> {
args[2] = e;
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index d2d026c9fb..33e4ba102f 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -945,7 +945,7 @@ public class RexImpTable {
BuiltInMethod.JSON_EXISTS3.method);
map.put(JSON_VALUE,
new JsonValueImplementor(BuiltInMethod.JSON_VALUE.method));
- defineReflective(JSON_QUERY, BuiltInMethod.JSON_QUERY.method);
+ map.put(JSON_QUERY, new
JsonQueryImplementor(BuiltInMethod.JSON_QUERY.method));
defineMethod(JSON_TYPE, BuiltInMethod.JSON_TYPE.method, NullPolicy.ARG0);
defineMethod(JSON_DEPTH, BuiltInMethod.JSON_DEPTH.method,
NullPolicy.ARG0);
defineMethod(JSON_INSERT, BuiltInMethod.JSON_INSERT.method,
NullPolicy.ARG0);
@@ -2903,6 +2903,34 @@ public class RexImpTable {
}
}
+ /**
+ * Implementor for JSON_QUERY function. Passes the jsonize flag depending on
the output type.
+ */
+ private static class JsonQueryImplementor extends MethodImplementor {
+ JsonQueryImplementor(Method method) {
+ super(method, NullPolicy.ARG0, false);
+ }
+
+ @Override Expression implementSafe(RexToLixTranslator translator,
+ RexCall call, List<Expression> argValueList) {
+ final List<Expression> newOperands = new ArrayList<>(argValueList);
+
+ final Expression jsonize;
+ if (SqlTypeUtil.inCharFamily(call.getType())) {
+ jsonize = TRUE_EXPR;
+ } else {
+ jsonize = FALSE_EXPR;
+ }
+ newOperands.add(jsonize);
+
+ List<Expression> argValueList0 =
+ EnumUtils.fromInternal(method.getParameterTypes(), newOperands);
+ final Expression target =
+ Expressions.new_(method.getDeclaringClass());
+ return Expressions.call(target, method, argValueList0);
+ }
+ }
+
/** Implementor for binary operators. */
private static class BinaryImplementor extends AbstractRexCallImplementor {
/** Types that can be arguments to comparison operators such as
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index 6b531c06ec..d4787a8100 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -993,6 +993,9 @@ public interface CalciteResource {
@BaseMessage("Illegal error behavior ''{0}'' specified in JSON_VALUE
function")
ExInst<CalciteException> illegalErrorBehaviorInJsonQueryFunc(String
errorBehavior);
+ @BaseMessage("EMPTY_OBJECT is illegal for given return type")
+ ExInst<CalciteException> illegalEmptyObjectInJsonQueryFunc();
+
@BaseMessage("Null key of JSON object is not allowed")
ExInst<CalciteException> nullKeyOfJsonObjectNotAllowed();
diff --git a/core/src/main/java/org/apache/calcite/runtime/JsonFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/JsonFunctions.java
index 6cd9258633..b77633924f 100644
--- a/core/src/main/java/org/apache/calcite/runtime/JsonFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/JsonFunctions.java
@@ -310,30 +310,35 @@ public class JsonFunctions {
}
}
- public @Nullable String jsonQuery(String input,
+ public @Nullable Object jsonQuery(
+ String input,
String pathSpec,
SqlJsonQueryWrapperBehavior wrapperBehavior,
SqlJsonQueryEmptyOrErrorBehavior emptyBehavior,
- SqlJsonQueryEmptyOrErrorBehavior errorBehavior) {
+ SqlJsonQueryEmptyOrErrorBehavior errorBehavior,
+ boolean jsonize) {
return jsonQuery(
jsonApiCommonSyntaxWithCache(input, pathSpec),
- wrapperBehavior, emptyBehavior, errorBehavior);
+ wrapperBehavior, emptyBehavior, errorBehavior, jsonize);
}
- public @Nullable String jsonQuery(JsonValueContext input,
+ public @Nullable Object jsonQuery(JsonValueContext input,
String pathSpec,
SqlJsonQueryWrapperBehavior wrapperBehavior,
SqlJsonQueryEmptyOrErrorBehavior emptyBehavior,
- SqlJsonQueryEmptyOrErrorBehavior errorBehavior) {
+ SqlJsonQueryEmptyOrErrorBehavior errorBehavior,
+ boolean jsonize) {
return jsonQuery(
jsonApiCommonSyntax(input, pathSpec),
- wrapperBehavior, emptyBehavior, errorBehavior);
+ wrapperBehavior, emptyBehavior, errorBehavior, jsonize);
}
- public @Nullable String jsonQuery(JsonPathContext context,
+ public @Nullable Object jsonQuery(
+ JsonPathContext context,
SqlJsonQueryWrapperBehavior wrapperBehavior,
SqlJsonQueryEmptyOrErrorBehavior emptyBehavior,
- SqlJsonQueryEmptyOrErrorBehavior errorBehavior) {
+ SqlJsonQueryEmptyOrErrorBehavior errorBehavior,
+ boolean jsonize) {
final Exception exc;
if (context.hasException()) {
exc = context.exc;
@@ -369,9 +374,9 @@ public class JsonFunctions {
case NULL:
return null;
case EMPTY_ARRAY:
- return "[]";
+ return jsonQueryEmptyArray(jsonize);
case EMPTY_OBJECT:
- return "{}";
+ return jsonQueryEmptyObject(jsonize);
default:
throw RESOURCE.illegalEmptyBehaviorInJsonQueryFunc(
emptyBehavior.toString()).ex();
@@ -381,10 +386,14 @@ public class JsonFunctions {
RESOURCE.arrayOrObjectValueRequiredInStrictModeOfJsonQueryFunc(
value.toString()).ex();
} else {
- try {
- return jsonize(value);
- } catch (Exception e) {
- exc = e;
+ if (jsonize) {
+ try {
+ return jsonize(value);
+ } catch (Exception e) {
+ exc = e;
+ }
+ } else {
+ return value;
}
}
}
@@ -394,14 +403,26 @@ public class JsonFunctions {
case NULL:
return null;
case EMPTY_ARRAY:
- return "[]";
+ return jsonQueryEmptyArray(jsonize);
case EMPTY_OBJECT:
- return "{}";
+ return jsonQueryEmptyObject(jsonize);
default:
throw RESOURCE.illegalErrorBehaviorInJsonQueryFunc(
errorBehavior.toString()).ex();
}
}
+
+ private static Object jsonQueryEmptyArray(boolean jsonize) {
+ return jsonize ? "[]" : Collections.emptyList();
+ }
+
+ private static String jsonQueryEmptyObject(boolean jsonize) {
+ if (jsonize) {
+ return "{}";
+ } else {
+ throw RESOURCE.illegalEmptyObjectInJsonQueryFunc().ex();
+ }
+ }
}
public static String jsonObject(SqlJsonConstructorNullClause nullClause,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonQueryFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonQueryFunction.java
index f44072d7dd..31f2329735 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonQueryFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonQueryFunction.java
@@ -16,6 +16,9 @@
*/
package org.apache.calcite.sql.fun;
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeFactory;
+import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
@@ -24,15 +27,25 @@ import org.apache.calcite.sql.SqlJsonQueryWrapperBehavior;
import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlOperatorBinding;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.OperandTypes;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlTypeFamily;
+import org.apache.calcite.sql.type.SqlTypeName;
import org.apache.calcite.sql.type.SqlTypeTransforms;
+import org.apache.calcite.sql.type.SqlTypeUtil;
+
+import com.google.common.collect.ImmutableList;
import org.checkerframework.checker.nullness.qual.Nullable;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Objects;
+import java.util.Optional;
+
import static java.util.Objects.requireNonNull;
/**
@@ -41,15 +54,45 @@ import static java.util.Objects.requireNonNull;
public class SqlJsonQueryFunction extends SqlFunction {
public SqlJsonQueryFunction() {
super("JSON_QUERY", SqlKind.OTHER_FUNCTION,
- ReturnTypes.VARCHAR_2000.andThen(SqlTypeTransforms.FORCE_NULLABLE),
+ ReturnTypes.cascade(
+ opBinding ->
+ explicitTypeSpec(opBinding)
+ .map(t -> deriveExplicitType(opBinding, t))
+ .orElseGet(() -> getDefaultType(opBinding)),
+ SqlTypeTransforms.FORCE_NULLABLE),
null,
- OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.CHARACTER,
- SqlTypeFamily.ANY, SqlTypeFamily.ANY, SqlTypeFamily.ANY),
+ OperandTypes.family(
+ ImmutableList.of(SqlTypeFamily.ANY, SqlTypeFamily.CHARACTER,
+ SqlTypeFamily.ANY, SqlTypeFamily.ANY, SqlTypeFamily.ANY,
SqlTypeFamily.ANY),
+ i -> i >= 5),
SqlFunctionCategory.SYSTEM);
}
+ /** Returns VARCHAR(2000) as default. */
+ private static RelDataType getDefaultType(SqlOperatorBinding opBinding) {
+ final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
+ final RelDataType baseType =
typeFactory.createSqlType(SqlTypeName.VARCHAR, 2000);
+ return typeFactory.createTypeWithNullability(baseType, true);
+ }
+
+ private static RelDataType deriveExplicitType(SqlOperatorBinding opBinding,
RelDataType type) {
+ if (SqlTypeName.ARRAY == type.getSqlTypeName()) {
+ RelDataType elementType =
Objects.requireNonNull(type.getComponentType());
+ RelDataType nullableElementType = deriveExplicitType(opBinding,
elementType);
+ return SqlTypeUtil.createArrayType(
+ opBinding.getTypeFactory(),
+ nullableElementType,
+ true);
+ }
+ return opBinding.getTypeFactory().createTypeWithNullability(type, true);
+ }
+
@Override public @Nullable String getSignatureTemplate(int operandsCount) {
- return "{0}({1} {2} {3} WRAPPER {4} ON EMPTY {5} ON ERROR)";
+ if (operandsCount == 6) {
+ return "{0}({1} {2} RETURNING {6} {3} WRAPPER {4} ON EMPTY {5} ON
ERROR)";
+ } else {
+ return "{0}({1} {2} {3} WRAPPER {4} ON EMPTY {5} ON ERROR)";
+ }
}
@Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
@@ -58,6 +101,10 @@ public class SqlJsonQueryFunction extends SqlFunction {
call.operand(0).unparse(writer, 0, 0);
writer.sep(",", true);
call.operand(1).unparse(writer, 0, 0);
+ if (call.operandCount() == 6) {
+ writer.keyword("RETURNING");
+ call.operand(5).unparse(writer, 0, 0);
+ }
final SqlJsonQueryWrapperBehavior wrapperBehavior =
getEnumValue(call.operand(2));
switch (wrapperBehavior) {
@@ -83,16 +130,32 @@ public class SqlJsonQueryFunction extends SqlFunction {
@Override public SqlCall createCall(@Nullable SqlLiteral functionQualifier,
SqlParserPos pos, @Nullable SqlNode... operands) {
+ final List<SqlNode> args = new ArrayList<>();
+ args.add(Objects.requireNonNull(operands[0]));
+ args.add(Objects.requireNonNull(operands[1]));
+
if (operands[2] == null) {
- operands[2] =
SqlLiteral.createSymbol(SqlJsonQueryWrapperBehavior.WITHOUT_ARRAY, pos);
+
args.add(SqlLiteral.createSymbol(SqlJsonQueryWrapperBehavior.WITHOUT_ARRAY,
pos));
+ } else {
+ args.add(operands[2]);
}
if (operands[3] == null) {
- operands[3] =
SqlLiteral.createSymbol(SqlJsonQueryEmptyOrErrorBehavior.NULL, pos);
+ args.add(SqlLiteral.createSymbol(SqlJsonQueryEmptyOrErrorBehavior.NULL,
pos));
+ } else {
+ args.add(operands[3]);
}
if (operands[4] == null) {
- operands[4] =
SqlLiteral.createSymbol(SqlJsonQueryEmptyOrErrorBehavior.NULL, pos);
+ args.add(SqlLiteral.createSymbol(SqlJsonQueryEmptyOrErrorBehavior.NULL,
pos));
+ } else {
+ args.add(operands[4]);
}
- return super.createCall(functionQualifier, pos, operands);
+
+ if (operands.length >= 6 && operands[5] != null) {
+ args.add(operands[5]);
+ }
+
+ pos = pos.plusAll(operands);
+ return new SqlBasicCall(this, args, pos, functionQualifier);
}
private static void unparseEmptyOrErrorBehavior(SqlWriter writer,
@@ -119,4 +182,20 @@ public class SqlJsonQueryFunction extends SqlFunction {
private static <E extends Enum<E>> E getEnumValue(SqlNode operand) {
return (E) requireNonNull(((SqlLiteral) operand).getValue(),
"operand.value");
}
+
+ public static boolean hasExplicitTypeSpec(List<SqlNode> operands) {
+ return operands.size() >= 6;
+ }
+
+ public static List<SqlNode> removeTypeSpecOperands(SqlCall call) {
+ return call.getOperandList().subList(0, 5);
+ }
+
+ /** Returns the optional explicit returning type specification. * */
+ private static Optional<RelDataType> explicitTypeSpec(SqlOperatorBinding
opBinding) {
+ if (opBinding.getOperandCount() >= 6) {
+ return Optional.of(opBinding.getOperandType(5));
+ }
+ return Optional.empty();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index 1490229ff9..f3360d2e31 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -60,6 +60,7 @@ import org.apache.calcite.sql.fun.SqlCastFunction;
import org.apache.calcite.sql.fun.SqlDatetimeSubtractionOperator;
import org.apache.calcite.sql.fun.SqlExtractFunction;
import org.apache.calcite.sql.fun.SqlInternalOperators;
+import org.apache.calcite.sql.fun.SqlJsonQueryFunction;
import org.apache.calcite.sql.fun.SqlJsonValueFunction;
import org.apache.calcite.sql.fun.SqlLibrary;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
@@ -92,6 +93,8 @@ import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
+import java.util.function.Function;
+import java.util.function.Predicate;
import java.util.function.UnaryOperator;
import java.util.stream.Collectors;
@@ -914,19 +917,42 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
}
public RexNode convertJsonValueFunction(
+ SqlRexContext cx, SqlJsonValueFunction fun, SqlCall call) {
+ return convertJsonReturningFunction(
+ cx,
+ fun,
+ call,
+ SqlJsonValueFunction::hasExplicitTypeSpec,
+ SqlJsonValueFunction::removeTypeSpecOperands);
+ }
+
+ public RexNode convertJsonQueryFunction(
+ SqlRexContext cx, SqlJsonQueryFunction fun, SqlCall call) {
+ return convertJsonReturningFunction(
+ cx,
+ fun,
+ call,
+ SqlJsonQueryFunction::hasExplicitTypeSpec,
+ SqlJsonQueryFunction::removeTypeSpecOperands);
+ }
+
+ public RexNode convertJsonReturningFunction(
SqlRexContext cx,
- SqlJsonValueFunction fun,
- SqlCall call) {
+ SqlFunction fun,
+ SqlCall call,
+ Predicate<List<SqlNode>> hasExplicitTypeSpec,
+ Function<SqlCall, List<SqlNode>> removeTypeSpecOperands) {
// For Expression with explicit return type:
- // i.e. json_value('{"foo":"bar"}', 'lax $.foo', returning varchar(2000))
+ // i.e. json_query('{"foo":"bar"}', 'lax $.foo', returning varchar(2000))
// use the specified type as the return type.
- List<SqlNode> operands =
- SqlJsonValueFunction.removeTypeSpecOperands(call);
+ List<SqlNode> operands = call.getOperandList();
+ boolean hasExplicitReturningType = hasExplicitTypeSpec.test(operands);
+ if (hasExplicitReturningType) {
+ operands = removeTypeSpecOperands.apply(call);
+ }
final List<RexNode> exprs =
- convertOperands(cx, call, operands,
- SqlOperandTypeChecker.Consistency.NONE);
- RelDataType returnType =
- cx.getValidator().getValidatedNodeTypeIfKnown(call);
+ convertOperands(cx, call, operands,
SqlOperandTypeChecker.Consistency.NONE);
+ RelDataType returnType =
cx.getValidator().getValidatedNodeTypeIfKnown(call);
requireNonNull(returnType, () -> "Unable to get type of " + call);
return cx.getRexBuilder().makeCall(returnType, fun, exprs);
}
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 2e72df1be6..d13dada6a6 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -423,7 +423,8 @@ public enum BuiltInMethod {
JSON_QUERY(JsonFunctions.StatefulFunction.class, "jsonQuery", String.class,
String.class, SqlJsonQueryWrapperBehavior.class,
SqlJsonQueryEmptyOrErrorBehavior.class,
- SqlJsonQueryEmptyOrErrorBehavior.class),
+ SqlJsonQueryEmptyOrErrorBehavior.class,
+ boolean.class),
JSON_OBJECT(JsonFunctions.class, "jsonObject",
SqlJsonConstructorNullClause.class),
JSON_TYPE(JsonFunctions.class, "jsonType", String.class),
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index ab88f0acba..9aaf1ded9d 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -324,6 +324,7 @@ EmptyResultOfJsonQueryFuncNotAllowed=Empty result of
JSON_QUERY function is not
IllegalEmptyBehaviorInJsonQueryFunc=Illegal empty behavior ''{0}'' specified
in JSON_VALUE function
ArrayOrObjectValueRequiredInStrictModeOfJsonQueryFunc=Strict jsonpath mode
requires array or object value, and the actual value is: ''{0}''
IllegalErrorBehaviorInJsonQueryFunc=Illegal error behavior ''{0}'' specified
in JSON_VALUE function
+IllegalEmptyObjectInJsonQueryFunc=EMPTY_OBJECT is illegal for given return type
NullKeyOfJsonObjectNotAllowed=Null key of JSON object is not allowed
QueryExecutionTimeoutReached=Timeout of ''{0}'' ms for query execution is
reached. Query execution started at ''{1}''
AmbiguousSortOrderInJsonArrayAggFunc=Including both WITHIN GROUP(...) and
inside ORDER BY in a single JSON_ARRAYAGG call is not allowed
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 980594a195..352491b1a5 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -8047,6 +8047,71 @@ public class JdbcTest {
.returns("C1=OBJECT; C2=ARRAY; C3=INTEGER; C4=BOOLEAN\n");
}
+ @Test void testJsonQuery() {
+ CalciteAssert.that()
+ .query("SELECT JSON_QUERY(v, '$.a') AS c1\n"
+ + ",JSON_QUERY(v, '$.a' RETURNING INTEGER ARRAY) AS c2\n"
+ + ",JSON_QUERY(v, '$.b' RETURNING INTEGER ARRAY EMPTY ARRAY ON
ERROR) AS c3\n"
+ + ",JSON_QUERY(v, '$.b' RETURNING VARCHAR ARRAY WITH ARRAY
WRAPPER) AS c4\n"
+ + "FROM (VALUES ('{\"a\": [1, 2],\"b\": \"[1, 2]\"}')) AS t(v)\n"
+ + "LIMIT 10")
+ .returns("C1=[1,2]; C2=[1, 2]; C3=[]; C4=[[1, 2]]\n");
+ }
+
+ @Test void testJsonValueError() {
+ java.sql.SQLException t =
+ assertThrows(
+ java.sql.SQLException.class,
+ () -> CalciteAssert.that()
+ .query("SELECT JSON_VALUE(v, 'lax $.a' RETURNING INTEGER) AS
c1\n"
+ + "FROM (VALUES ('{\"a\": \"abc\"}')) AS t(v)\n"
+ + "LIMIT 10")
+ .returns(""));
+
+ assertThat(
+ t.getMessage(), containsString("java.lang.String cannot be cast to"));
+ }
+
+ @Test void testJsonQueryError() {
+ java.sql.SQLException t =
+ assertThrows(
+ java.sql.SQLException.class,
+ () -> CalciteAssert.that()
+ .query("SELECT JSON_QUERY(v, '$.a' RETURNING VARCHAR ARRAY"
+ + " EMPTY OBJECT ON ERROR) AS c1\n"
+ + "FROM (VALUES ('{\"a\": \"hi\"}')) AS t(v)\n"
+ + "LIMIT 10")
+ .returns(""));
+
+ assertThat(
+ t.getMessage(), containsString("EMPTY_OBJECT is illegal for given
return type"));
+
+ t =
+ assertThrows(
+ java.sql.SQLException.class,
+ () -> CalciteAssert.that()
+ .query("SELECT JSON_QUERY(v, 'lax $.a' RETURNING VARCHAR ARRAY"
+ + " EMPTY OBJECT ON EMPTY) AS c1\n"
+ + "FROM (VALUES ('{\"a\": null}')) AS t(v)\n"
+ + "LIMIT 10")
+ .returns(""));
+
+ assertThat(
+ t.getMessage(), containsString("EMPTY_OBJECT is illegal for given
return type"));
+
+ t =
+ assertThrows(
+ java.sql.SQLException.class,
+ () -> CalciteAssert.that()
+ .query("SELECT JSON_QUERY(v, 'lax $.a' RETURNING INTEGER) AS
c1\n"
+ + "FROM (VALUES ('{\"a\": [\"a\", \"b\"]}')) AS t(v)\n"
+ + "LIMIT 10")
+ .returns(""));
+
+ assertThat(
+ t.getMessage(), containsString("java.util.ArrayList cannot be cast
to"));
+ }
+
@Test void testJsonDepth() {
CalciteAssert.that()
.query("SELECT JSON_DEPTH(v) AS c1\n"
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
b/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
index b69f30d804..0879bdee0a 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
@@ -413,6 +413,26 @@ class SqlJsonFunctionsTest {
SqlJsonQueryEmptyOrErrorBehavior.NULL,
SqlJsonQueryEmptyOrErrorBehavior.NULL,
is("[\"bar\"]"));
+
+ // jsonize test
+
+ assertJsonQuery(
+ JsonFunctions.JsonPathContext
+ .withJavaObj(JsonFunctions.PathMode.STRICT,
+ Collections.singletonList("bar")),
+ SqlJsonQueryWrapperBehavior.WITH_CONDITIONAL_ARRAY,
+ SqlJsonQueryEmptyOrErrorBehavior.NULL,
+ SqlJsonQueryEmptyOrErrorBehavior.NULL,
+ false,
+ is(Collections.singletonList("bar")));
+ assertJsonQuery(
+ JsonFunctions.JsonPathContext
+ .withUnknownException(new Exception("test message")),
+ SqlJsonQueryWrapperBehavior.WITH_CONDITIONAL_ARRAY,
+ SqlJsonQueryEmptyOrErrorBehavior.EMPTY_ARRAY,
+ SqlJsonQueryEmptyOrErrorBehavior.EMPTY_ARRAY,
+ false,
+ is(Collections.emptyList()));
}
@Test void testJsonize() {
@@ -706,14 +726,23 @@ class SqlJsonFunctionsTest {
SqlJsonQueryWrapperBehavior wrapperBehavior,
SqlJsonQueryEmptyOrErrorBehavior emptyBehavior,
SqlJsonQueryEmptyOrErrorBehavior errorBehavior,
- Matcher<? super String> matcher) {
+ Matcher<? super Object> matcher) {
+ assertJsonQuery(input, wrapperBehavior, emptyBehavior, errorBehavior,
true, matcher);
+ }
+
+ private void assertJsonQuery(JsonFunctions.JsonPathContext input,
+ SqlJsonQueryWrapperBehavior wrapperBehavior,
+ SqlJsonQueryEmptyOrErrorBehavior emptyBehavior,
+ SqlJsonQueryEmptyOrErrorBehavior errorBehavior,
+ boolean jsonize,
+ Matcher<? super Object> matcher) {
final JsonFunctions.StatefulFunction f =
new JsonFunctions.StatefulFunction();
assertThat(
invocationDesc(BuiltInMethod.JSON_QUERY, input, wrapperBehavior,
emptyBehavior, errorBehavior),
f.jsonQuery(input, wrapperBehavior, emptyBehavior,
- errorBehavior),
+ errorBehavior, jsonize),
matcher);
}
@@ -728,7 +757,7 @@ class SqlJsonFunctionsTest {
invocationDesc(BuiltInMethod.JSON_QUERY, input, wrapperBehavior,
emptyBehavior, errorBehavior),
() -> f.jsonQuery(input, wrapperBehavior, emptyBehavior,
- errorBehavior),
+ errorBehavior, true),
matcher);
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 21d29ebe79..ea61907ee8 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -11487,6 +11487,14 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
expr("json_query('{\"foo\":\"bar\"}', 'strict $' EMPTY OBJECT ON EMPTY "
+ "EMPTY ARRAY ON ERROR EMPTY ARRAY ON EMPTY NULL ON ERROR)")
.columnType("VARCHAR(2000)");
+
+ expr("json_query('{\"foo\":[100, null, 200]}', 'lax $.foo'"
+ + "returning integer array)")
+ .columnType("INTEGER ARRAY");
+
+ expr("json_query('{\"foo\":[[100, null, 200]]}', 'lax $.foo'"
+ + "returning integer array array)")
+ .columnType("INTEGER ARRAY ARRAY");
}
@Test void testJsonArray() {
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index ae0234fab5..c0bfb3bbb7 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -8822,6 +8822,9 @@ public class SqlParserTest {
+ "EMPTY OBJECT ON ERROR)")
.ok("JSON_QUERY('{\"foo\": \"bar\"}', "
+ "'lax $' WITHOUT ARRAY WRAPPER EMPTY ARRAY ON EMPTY EMPTY OBJECT
ON ERROR)");
+ expr("json_query('{\"foo\": \"bar\"}', 'lax $' RETURNING VARCHAR ARRAY
WITHOUT ARRAY WRAPPER)")
+ .ok("JSON_QUERY('{\"foo\": \"bar\"}', "
+ + "'lax $' RETURNING VARCHAR ARRAY WITHOUT ARRAY WRAPPER NULL ON
EMPTY NULL ON ERROR)");
}
@Test void testJsonObject() {
