You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by ho...@apache.org on 2019/03/28 03:48:42 UTC
[calcite] branch master updated: [CALCITE-2808] Add the JSON_LENGTH
function (xuqianjin)
This is an automated email from the ASF dual-hosted git repository.
hongze pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push:
new 2d33c57 [CALCITE-2808] Add the JSON_LENGTH function (xuqianjin)
2d33c57 is described below
commit 2d33c57bff41b6ea473f8f60505a10b196ea79d7
Author: XuQianJin-Stars <x1...@163.com>
AuthorDate: Tue Mar 5 18:35:24 2019 +0800
[CALCITE-2808] Add the JSON_LENGTH function (xuqianjin)
Close apache/calcite#1070
---
babel/src/main/codegen/config.fmpp | 5 +-
core/src/main/codegen/config.fmpp | 1 +
core/src/main/codegen/templates/Parser.jj | 50 ++++++++++++----
.../calcite/adapter/enumerable/RexImpTable.java | 7 ++-
.../apache/calcite/runtime/.SqlFunctions.java.swp | Bin 0 -> 16384 bytes
.../apache/calcite/runtime/CalciteResource.java | 14 +++--
.../org/apache/calcite/runtime/SqlFunctions.java | 62 ++++++++++++++++++--
.../sql/fun/SqlJsonApiCommonSyntaxOperator.java | 34 ++++++++---
.../calcite/sql/fun/SqlJsonLengthFunction.java | 65 +++++++++++++++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 7 ++-
.../org/apache/calcite/util/BuiltInMethod.java | 3 +
.../calcite/runtime/CalciteResource.properties | 6 +-
core/src/test/codegen/config.fmpp | 1 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 9 +++
.../apache/calcite/sql/parser/SqlParserTest.java | 11 ++++
.../calcite/sql/test/SqlOperatorBaseTest.java | 54 ++++++++++++++++-
.../java/org/apache/calcite/test/JdbcTest.java | 25 +++++---
.../apache/calcite/test/SqlJsonFunctionsTest.java | 36 ++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 9 +++
server/src/main/codegen/config.fmpp | 1 +
site/_docs/reference.md | 21 +++++++
21 files changed, 378 insertions(+), 43 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp b/babel/src/main/codegen/config.fmpp
index dab4251..5439d14 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -141,6 +141,8 @@ data: {
"JSON"
"JSON_TYPE"
"JSON_DEPTH"
+ "JSON_LENGTH"
+ "JSON_PRETTY"
"K"
"KEY"
"KEY_MEMBER"
@@ -555,9 +557,6 @@ data: {
"JSON_ARRAYAGG",
"JSON_EXISTS",
"JSON_VALUE",
- "JSON_TYPE",
- "JSON_DEPTH"
- "JSON_PRETTY",
"JSON_OBJECT",
"JSON_OBJECTAGG",
"JSON_QUERY",
diff --git a/core/src/main/codegen/config.fmpp b/core/src/main/codegen/config.fmpp
index c8f9ec8..7b3fca2 100644
--- a/core/src/main/codegen/config.fmpp
+++ b/core/src/main/codegen/config.fmpp
@@ -161,6 +161,7 @@ data: {
"JSON"
"JSON_TYPE"
"JSON_DEPTH"
+ "JSON_LENGTH"
"JSON_PRETTY"
"K"
"KEY"
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 98e3f1b..5dfa66e 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -4864,6 +4864,8 @@ SqlNode BuiltinFunctionCall() :
|
node = JsonDepthFunctionCall() { return node; }
|
+ node = JsonLengthFunctionCall() { return node; }
+ |
node = JsonObjectAggFunctionCall() { return node; }
|
node = JsonArrayFunctionCall() { return node; }
@@ -4958,21 +4960,32 @@ SqlNode JsonPathSpec() :
}
}
-SqlNode JsonApiCommonSyntax() :
+SqlNode JsonApiCommonSyntax(boolean acceptNonPath) :
{
SqlNode e;
List<SqlNode> args = new ArrayList<SqlNode>();
Span span;
+ SqlOperator op;
}
{
e = JsonValueExpression(true) {
args.add(e);
span = Span.of(e);
}
- <COMMA>
- e = Expression(ExprContext.ACCEPT_NON_QUERY) {
- args.add(e);
- }
+ (
+ <COMMA>
+ e = Expression(ExprContext.ACCEPT_NON_QUERY) {
+ op = SqlStdOperatorTable.JSON_API_COMMON_SYNTAX;
+ args.add(e);
+ }
+ |
+ {
+ if (!acceptNonPath) {
+ throw new ParseException(RESOURCE.jsonPathMustBeSpecified().str());
+ }
+ op = SqlStdOperatorTable.JSON_API_COMMON_SYNTAX_WITHOUT_PATH;
+ }
+ )
[
<PASSING> e = JsonValueExpression(false) {
args.add(e);
@@ -4991,9 +5004,8 @@ SqlNode JsonApiCommonSyntax() :
)*
]
{
- return SqlStdOperatorTable.JSON_API_COMMON_SYNTAX.createCall(span.end(this), args);
+ return op.createCall(span.end(this), args);
}
-
}
SqlJsonExistsErrorBehavior JsonExistsErrorBehavior() :
@@ -5019,7 +5031,7 @@ SqlCall JsonExistsFunctionCall() :
}
{
<JSON_EXISTS> { span = span(); }
- <LPAREN> e = JsonApiCommonSyntax() {
+ <LPAREN> e = JsonApiCommonSyntax(false) {
args = new ArrayList<SqlNode>();
args.add(e);
}
@@ -5076,7 +5088,7 @@ SqlCall JsonValueFunctionCall() :
}
{
<JSON_VALUE> { span = span(); }
- <LPAREN> e = JsonApiCommonSyntax() {
+ <LPAREN> e = JsonApiCommonSyntax(false) {
args[0] = e;
}
[
@@ -5185,7 +5197,7 @@ SqlCall JsonQueryFunctionCall() :
}
{
<JSON_QUERY> { span = span(); }
- <LPAREN> e = JsonApiCommonSyntax() {
+ <LPAREN> e = JsonApiCommonSyntax(false) {
args[0] = e;
}
[
@@ -5331,6 +5343,23 @@ SqlCall JsonDepthFunctionCall() :
}
}
+SqlCall JsonLengthFunctionCall() :
+{
+ final SqlNode[] args = new SqlNode[1];
+ SqlNode e;
+ final Span span;
+ List<SqlNode> behavior;
+}
+{
+ <JSON_LENGTH> { span = span(); }
+ <LPAREN> e = JsonApiCommonSyntax(true) {
+ args[0] = e;
+ }
+ <RPAREN> {
+ return SqlStdOperatorTable.JSON_LENGTH.createCall(span.end(this), args);
+ }
+}
+
SqlCall JsonObjectAggFunctionCall() :
{
final SqlNode[] args = new SqlNode[2];
@@ -6408,6 +6437,7 @@ SqlPostfixOperator PostfixRowOperator() :
| < JSON_OBJECT: "JSON_OBJECT">
| < JSON_TYPE: "JSON_TYPE">
| < JSON_DEPTH: "JSON_DEPTH">
+| < JSON_LENGTH: "JSON_LENGTH">
| < JSON_OBJECTAGG: "JSON_OBJECTAGG">
| < JSON_QUERY: "JSON_QUERY" >
| < K: "K" >
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 e5679b2..298a5c0 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
@@ -162,10 +162,12 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.IS_NULL;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.IS_TRUE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ITEM;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_API_COMMON_SYNTAX;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_API_COMMON_SYNTAX_WITHOUT_PATH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_ARRAY;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_ARRAYAGG;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_DEPTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_EXISTS;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_LENGTH;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_OBJECT;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_OBJECTAGG;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.JSON_PRETTY;
@@ -454,7 +456,9 @@ public class RexImpTable {
defineMethod(JSON_STRUCTURED_VALUE_EXPRESSION,
BuiltInMethod.JSON_STRUCTURED_VALUE_EXPRESSION.method, NullPolicy.STRICT);
defineMethod(JSON_API_COMMON_SYNTAX, BuiltInMethod.JSON_API_COMMON_SYNTAX.method,
- NullPolicy.NONE);
+ NullPolicy.NONE);
+ defineMethod(JSON_API_COMMON_SYNTAX_WITHOUT_PATH,
+ BuiltInMethod.JSON_API_COMMON_SYNTAX_WITHOUT_PATH.method, NullPolicy.NONE);
defineMethod(JSON_EXISTS, BuiltInMethod.JSON_EXISTS.method, NullPolicy.NONE);
defineMethod(JSON_VALUE_ANY, BuiltInMethod.JSON_VALUE_ANY.method, NullPolicy.NONE);
defineMethod(JSON_QUERY, BuiltInMethod.JSON_QUERY.method, NullPolicy.NONE);
@@ -463,6 +467,7 @@ public class RexImpTable {
defineMethod(JSON_TYPE, BuiltInMethod.JSON_TYPE.method, NullPolicy.NONE);
defineMethod(JSON_DEPTH, BuiltInMethod.JSON_DEPTH.method, NullPolicy.NONE);
defineMethod(JSON_PRETTY, BuiltInMethod.JSON_PRETTY.method, NullPolicy.NONE);
+ defineMethod(JSON_LENGTH, BuiltInMethod.JSON_LENGTH.method, NullPolicy.NONE);
aggMap.put(JSON_OBJECTAGG.with(SqlJsonConstructorNullClause.ABSENT_ON_NULL),
JsonObjectAggImplementor
.supplierFor(BuiltInMethod.JSON_OBJECTAGG_ADD.method));
diff --git a/core/src/main/java/org/apache/calcite/runtime/.SqlFunctions.java.swp b/core/src/main/java/org/apache/calcite/runtime/.SqlFunctions.java.swp
new file mode 100644
index 0000000..d1376b8
Binary files /dev/null and b/core/src/main/java/org/apache/calcite/runtime/.SqlFunctions.java.swp differ
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 e356e84..53b2480 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -43,6 +43,9 @@ public interface CalciteResource {
@BaseMessage("APPLY operator is not allowed under the current SQL conformance level")
ExInst<CalciteException> applyNotAllowed();
+ @BaseMessage("JSON path expression must be specified after the JSON value expression")
+ ExInst<CalciteException> jsonPathMustBeSpecified();
+
@BaseMessage("Illegal {0} literal {1}: {2}")
ExInst<CalciteException> illegalLiteral(String a0, String a1, String a2);
@@ -862,14 +865,17 @@ public interface CalciteResource {
@BaseMessage("While executing SQL [{0}] on JDBC sub-schema")
ExInst<RuntimeException> exceptionWhilePerformingQueryOnJdbcSubSchema(String sql);
- @BaseMessage("Unknown JSON type in JSON_TYPE function, and the object is: ''{0}''")
- ExInst<CalciteException> unknownObjectOfJsonType(String value);
+ @BaseMessage("Not a valid input for JSON_TYPE: ''{0}''")
+ ExInst<CalciteException> invalidInputForJsonType(String value);
- @BaseMessage("Unknown JSON depth in JSON_DEPTH function, and the object is: ''{0}''")
- ExInst<CalciteException> unknownObjectOfJsonDepth(String value);
+ @BaseMessage("Not a valid input for JSON_DEPTH: ''{0}''")
+ ExInst<CalciteException> invalidInputForJsonDepth(String value);
@BaseMessage("Cannot serialize object to JSON, and the object is: ''{0}''")
ExInst<CalciteException> exceptionWhileSerializingToJson(String value);
+
+ @BaseMessage("Not a valid input for JSON_LENGTH: ''{0}''")
+ ExInst<CalciteException> invalidInputForJsonLength(String value);
}
// End CalciteResource.java
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 50079cb..c508ea0 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2460,6 +2460,10 @@ public class SqlFunctions {
return input;
}
+ public static PathContext jsonApiCommonSyntax(Object input) {
+ return jsonApiCommonSyntax(input, "strict $");
+ }
+
public static PathContext jsonApiCommonSyntax(Object input, String pathSpec) {
try {
Matcher matcher = JSON_PATH_BASE.matcher(pathSpec);
@@ -2755,12 +2759,12 @@ public class SqlFunctions {
result = "unknown";
}
if (result.equals("unknown")) {
- throw RESOURCE.unknownObjectOfJsonType(o.toString()).ex();
+ throw RESOURCE.invalidInputForJsonType(o.toString()).ex();
} else {
return result;
}
} catch (Exception ex) {
- throw RESOURCE.unknownObjectOfJsonType(o.toString()).ex();
+ throw RESOURCE.invalidInputForJsonType(o.toString()).ex();
}
}
@@ -2774,7 +2778,7 @@ public class SqlFunctions {
}
return result;
} catch (Exception ex) {
- throw RESOURCE.unknownObjectOfJsonDepth(o.toString()).ex();
+ throw RESOURCE.invalidInputForJsonDepth(o.toString()).ex();
}
}
@@ -2806,6 +2810,49 @@ public class SqlFunctions {
return depth;
}
+ public static Integer jsonLength(Object input) {
+ final Integer result;
+ final Object value;
+ try {
+ if (!isJsonPathContext(input)) {
+ throw RESOURCE.invalidInputForJsonLength(
+ input.toString()).ex();
+ }
+ PathContext context = (PathContext) input;
+ if (context.exc != null) {
+ throw toUnchecked(context.exc);
+ }
+ value = context.pathReturned;
+
+ if (value == null) {
+ result = null;
+ } else {
+ if (value instanceof Collection) {
+ result = ((Collection) value).size();
+ } else if (value instanceof Map) {
+ result = ((LinkedHashMap) value).size();
+ } else if (isScalarObject(value)) {
+ result = 1;
+ } else {
+ result = 0;
+ }
+ }
+ } catch (Exception ex) {
+ throw RESOURCE.invalidInputForJsonLength(
+ input.toString()).ex();
+ }
+ return result;
+ }
+
+ public static boolean isJsonPathContext(Object input) {
+ try {
+ PathContext context = (PathContext) input;
+ return context != null;
+ } catch (Exception e) {
+ return false;
+ }
+ }
+
public static boolean isJsonValue(String input) {
try {
dejsonize(input);
@@ -2857,6 +2904,12 @@ public class SqlFunctions {
public final Object pathReturned;
public final Exception exc;
+ private PathContext(Object pathReturned, Exception exc) {
+ this.mode = PathMode.NONE;
+ this.pathReturned = pathReturned;
+ this.exc = exc;
+ }
+
private PathContext(PathMode mode, Object pathReturned, Exception exc) {
this.mode = mode;
this.pathReturned = pathReturned;
@@ -2898,7 +2951,8 @@ public class SqlFunctions {
public enum PathMode {
LAX,
STRICT,
- UNKNOWN
+ UNKNOWN,
+ NONE
}
/** Enumerates over the cartesian product of the given lists, returning
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonApiCommonSyntaxOperator.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonApiCommonSyntaxOperator.java
index de50336..72e4f80 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonApiCommonSyntaxOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonApiCommonSyntaxOperator.java
@@ -33,16 +33,31 @@ import org.apache.calcite.sql.validate.SqlValidator;
*/
public class SqlJsonApiCommonSyntaxOperator extends SqlSpecialOperator {
- public SqlJsonApiCommonSyntaxOperator() {
- super("JSON_API_COMMON_SYNTAX", SqlKind.JSON_API_COMMON_SYNTAX, 100, true,
+ // If true, the syntax must contain a JSON path expression, e.g. '{'foo':'bar'}', 'lax $.foo';
+ // otherwise the syntax can be specified within JSON text only, e.g. '{'foo':'bar'}'.
+ private final boolean hasPath;
+
+ public SqlJsonApiCommonSyntaxOperator(String name, boolean hasPath) {
+ super(name, SqlKind.JSON_API_COMMON_SYNTAX, 100, true,
ReturnTypes.explicit(SqlTypeName.ANY), null,
- OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.STRING));
+ hasPath ? OperandTypes.family(SqlTypeFamily.ANY, SqlTypeFamily.STRING)
+ : OperandTypes.family(SqlTypeFamily.ANY)
+ );
+ this.hasPath = hasPath;
}
@Override protected void checkOperandCount(SqlValidator validator,
SqlOperandTypeChecker argType, SqlCall call) {
- if (call.operandCount() != 2) {
- throw new UnsupportedOperationException("json passing syntax is not yet supported");
+ if (hasPath) {
+ if (call.operandCount() < 2) {
+ throw new UnsupportedOperationException(
+ "JSON API common syntax requires at least 2 parameters");
+ }
+ } else {
+ if (call.operandCount() < 1) {
+ throw new UnsupportedOperationException(
+ "JSON API common syntax requires at least 1 parameter");
+ }
}
}
@@ -50,9 +65,12 @@ public class SqlJsonApiCommonSyntaxOperator extends SqlSpecialOperator {
int rightPrec) {
SqlWriter.Frame frame = writer.startList(SqlWriter.FrameTypeEnum.SIMPLE);
call.operand(0).unparse(writer, 0, 0);
- writer.sep(",", true);
- call.operand(1).unparse(writer, 0, 0);
- if (call.operandCount() > 2) {
+ if (hasPath) {
+ writer.sep(",", true);
+ call.operand(1).unparse(writer, 0, 0);
+ }
+ if (hasPath && call.operandCount() > 2
+ || !hasPath && call.operandCount() > 1) {
writer.keyword("PASSING");
for (int i = 2; i < call.getOperandList().size(); i += 2) {
call.operand(i).unparse(writer, 0, 0);
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonLengthFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonLengthFunction.java
new file mode 100644
index 0000000..8140efd
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonLengthFunction.java
@@ -0,0 +1,65 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlFunction;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNode;
+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.SqlTypeTransforms;
+
+/**
+ * The <code>JSON_LENGTH</code> function.
+ */
+public class SqlJsonLengthFunction extends SqlFunction {
+ public SqlJsonLengthFunction() {
+ super("JSON_LENGTH", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.cascade(ReturnTypes.INTEGER,
+ SqlTypeTransforms.FORCE_NULLABLE),
+ null,
+ OperandTypes.ANY,
+ SqlFunctionCategory.SYSTEM);
+ }
+
+ @Override public String getSignatureTemplate(int operandsCount) {
+ assert operandsCount == 1 || operandsCount == 2;
+ if (operandsCount == 1) {
+ return "{0}({1})";
+ }
+ return "{0}({1} {2})";
+ }
+
+ @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
+ int rightPrec) {
+ final SqlWriter.Frame frame = writer.startFunCall(getName());
+ call.operand(0).unparse(writer, 0, 0);
+ writer.endFunCall(frame);
+ }
+
+ @Override public SqlCall createCall(SqlLiteral functionQualifier,
+ SqlParserPos pos, SqlNode... operands) {
+ return super.createCall(functionQualifier, pos, operands);
+ }
+}
+
+// End SqlJsonLengthFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 50e6eb1..5e89b88 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -1290,7 +1290,10 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
true);
public static final SqlJsonApiCommonSyntaxOperator JSON_API_COMMON_SYNTAX =
- new SqlJsonApiCommonSyntaxOperator();
+ new SqlJsonApiCommonSyntaxOperator("JSON_API_COMMON_SYNTAX", true);
+
+ public static final SqlJsonApiCommonSyntaxOperator JSON_API_COMMON_SYNTAX_WITHOUT_PATH =
+ new SqlJsonApiCommonSyntaxOperator("JSON_API_COMMON_SYNTAX_WITHOUT_PATH", false);
public static final SqlFunction JSON_EXISTS = new SqlJsonExistsFunction();
@@ -1311,6 +1314,8 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
public static final SqlFunction JSON_DEPTH = new SqlJsonDepthFunction();
+ public static final SqlFunction JSON_LENGTH = new SqlJsonLengthFunction();
+
public static final SqlJsonObjectAggAggFunction JSON_OBJECTAGG =
new SqlJsonObjectAggAggFunction(SqlKind.JSON_OBJECTAGG,
SqlJsonConstructorNullClause.NULL_ON_NULL);
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 962c3bf..e0add52 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -275,6 +275,8 @@ public enum BuiltInMethod {
"jsonStructuredValueExpression", Object.class),
JSON_API_COMMON_SYNTAX(SqlFunctions.class, "jsonApiCommonSyntax",
Object.class, String.class),
+ JSON_API_COMMON_SYNTAX_WITHOUT_PATH(SqlFunctions.class, "jsonApiCommonSyntax",
+ Object.class),
JSON_EXISTS(SqlFunctions.class, "jsonExists", Object.class),
JSON_VALUE_ANY(SqlFunctions.class, "jsonValueAny", Object.class,
SqlJsonValueEmptyOrErrorBehavior.class, Object.class,
@@ -288,6 +290,7 @@ public enum BuiltInMethod {
JSON_TYPE(SqlFunctions.class, "jsonType", Object.class),
JSON_DEPTH(SqlFunctions.class, "jsonDepth", Object.class),
JSON_PRETTY(SqlFunctions.class, "jsonPretty", Object.class),
+ JSON_LENGTH(SqlFunctions.class, "jsonLength", Object.class),
JSON_OBJECTAGG_ADD(SqlFunctions.class, "jsonObjectAggAdd", Map.class,
String.class, Object.class, SqlJsonConstructorNullClause.class),
JSON_ARRAY(SqlFunctions.class, "jsonArray",
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 bd31c82..d92b76a 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -21,6 +21,7 @@ BangEqualNotAllowed=Bang equal ''!='' is not allowed under the current SQL confo
PercentRemainderNotAllowed=Percent remainder ''%'' is not allowed under the current SQL conformance level
LimitStartCountNotAllowed=''LIMIT start, count'' is not allowed under the current SQL conformance level
ApplyNotAllowed=APPLY operator is not allowed under the current SQL conformance level
+JsonPathMustBeSpecified=JSON path expression must be specified after the JSON value expression
IllegalLiteral=Illegal {0} literal {1}: {2}
IdentifierTooLong=Length of identifier ''{0}'' must be less than or equal to {1,number,#} characters
BadFormat=not in format ''{0}''
@@ -280,7 +281,8 @@ 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
ExceptionWhilePerformingQueryOnJdbcSubSchema = While executing SQL [{0}] on JDBC sub-schema
-UnknownObjectOfJsonType=Unknown JSON type in JSON_TYPE function, and the object is: ''{0}''
-UnknownObjectOfJsonDepth=Unknown JSON depth in JSON_DEPTH function, and the object is: ''{0}''
+InvalidInputForJsonType=Not a valid input for JSON_TYPE: ''{0}''
+InvalidInputForJsonDepth=Not a valid input for JSON_DEPTH: ''{0}''
ExceptionWhileSerializingToJson=Cannot serialize object to JSON, and the object is: ''{0}''
+InvalidInputForJsonLength=Not a valid input for JSON_LENGTH: ''{0}''
# End CalciteResource.properties
diff --git a/core/src/test/codegen/config.fmpp b/core/src/test/codegen/config.fmpp
index d9ae98b..79e0315 100644
--- a/core/src/test/codegen/config.fmpp
+++ b/core/src/test/codegen/config.fmpp
@@ -145,6 +145,7 @@ data: {
"JSON"
"JSON_TYPE"
"JSON_DEPTH"
+ "JSON_LENGTH"
"JSON_PRETTY"
"K"
"KEY"
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 378c405..a6176fb 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -3230,6 +3230,15 @@ public class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ @Test public void testJsonLength() {
+ String query = "select json_length(\"product_name\", 'lax $'), "
+ + "json_length(\"product_name\") from \"product\"";
+ final String expected = "SELECT JSON_LENGTH(\"product_name\" FORMAT JSON, 'lax $'), "
+ + "JSON_LENGTH(\"product_name\" FORMAT JSON)\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query).ok(expected);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final SchemaPlus schema;
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index b53df4d..5e00000 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -8429,6 +8429,17 @@ public class SqlParserTest {
"JSON_DEPTH('{\"foo\": \"100\"}' FORMAT JSON)");
}
+ @Test public void testJsonLength() {
+ checkExp("json_length('{\"foo\": \"bar\"}')",
+ "JSON_LENGTH('{\"foo\": \"bar\"}' FORMAT JSON)");
+ checkExp("json_length('{\"foo\": \"bar\"}', 'lax $')",
+ "JSON_LENGTH('{\"foo\": \"bar\"}' FORMAT JSON, 'lax $')");
+ checkExp("json_length('{\"foo\": \"bar\"}', 'strict $')",
+ "JSON_LENGTH('{\"foo\": \"bar\"}' FORMAT JSON, 'strict $')");
+ checkExp("json_length('{\"foo\": \"bar\"}', 'invalid $')",
+ "JSON_LENGTH('{\"foo\": \"bar\"}' FORMAT JSON, 'invalid $')");
+ }
+
@Test public void testJsonObjectAgg() {
checkExp("json_objectagg(k_column: v_column)",
"JSON_OBJECTAGG(KEY `K_COLUMN` VALUE `V_COLUMN` NULL ON NULL)");
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
index d42ab3c..56e9ef3 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
@@ -4534,7 +4534,7 @@ public abstract class SqlOperatorBaseTest {
tester.checkString("json_depth('{}')",
"1", "INTEGER");
tester.checkString("json_depth('[]')",
- "1", "INTEGER");
+ "1", "INTEGER");
tester.checkString("json_depth('null')",
null, "INTEGER");
tester.checkString("json_depth(cast(null as varchar(1)))",
@@ -4542,13 +4542,63 @@ public abstract class SqlOperatorBaseTest {
tester.checkString("json_depth('[10, true]')",
"2", "INTEGER");
tester.checkString("json_depth('[[], {}]')",
- "2", "INTEGER");
+ "2", "INTEGER");
tester.checkString("json_depth('{\"a\": [10, true]}')",
"3", "INTEGER");
tester.checkString("json_depth('[10, {\"a\": [[1,2]]}]')",
"5", "INTEGER");
}
+ @Test public void testJsonLength() {
+ // no path context
+ tester.checkString("json_length('{}')",
+ "0", "INTEGER");
+ tester.checkString("json_length('[]')",
+ "0", "INTEGER");
+ tester.checkString("json_length('{\"foo\":100}')",
+ "1", "INTEGER");
+ tester.checkString("json_length('{\"a\": 1, \"b\": {\"c\": 30}}')",
+ "2", "INTEGER");
+ tester.checkString("json_length('[1, 2, {\"a\": 3}]')",
+ "3", "INTEGER");
+
+ // lax test
+ tester.checkString("json_length('{}', 'lax $')",
+ "0", "INTEGER");
+ tester.checkString("json_length('[]', 'lax $')",
+ "0", "INTEGER");
+ tester.checkString("json_length('{\"foo\":100}', 'lax $')",
+ "1", "INTEGER");
+ tester.checkString("json_length('{\"a\": 1, \"b\": {\"c\": 30}}', 'lax $')",
+ "2", "INTEGER");
+ tester.checkString("json_length('[1, 2, {\"a\": 3}]', 'lax $')",
+ "3", "INTEGER");
+ tester.checkString("json_length('{\"a\": 1, \"b\": {\"c\": 30}}', 'lax $.b')",
+ "1", "INTEGER");
+ tester.checkString("json_length('{\"foo\":100}', 'lax $.foo1')",
+ null, "INTEGER");
+
+ // strict test
+ tester.checkString("json_length('{}', 'strict $')",
+ "0", "INTEGER");
+ tester.checkString("json_length('[]', 'strict $')",
+ "0", "INTEGER");
+ tester.checkString("json_length('{\"foo\":100}', 'strict $')",
+ "1", "INTEGER");
+ tester.checkString("json_length('{\"a\": 1, \"b\": {\"c\": 30}}', 'strict $')",
+ "2", "INTEGER");
+ tester.checkString("json_length('[1, 2, {\"a\": 3}]', 'strict $')",
+ "3", "INTEGER");
+ tester.checkString("json_length('{\"a\": 1, \"b\": {\"c\": 30}}', 'strict $.b')",
+ "1", "INTEGER");
+
+ // catch error test
+ tester.checkFails("json_length('{\"foo\":100}', 'invalid $.foo')",
+ "(?s).*Illegal jsonpath spec.*", true);
+ tester.checkFails("json_length('{\"foo\":100}', 'strict $.foo1')",
+ "(?s).*No results for path.*", true);
+ }
+
@Test public void testJsonObjectAgg() {
checkAggType(tester, "json_objectagg('foo': 'bar')", "VARCHAR(2000) NOT NULL");
tester.checkFails("^json_objectagg(100: 'bar')^",
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 e39a81a..cfd34c9 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -6795,7 +6795,6 @@ public class JdbcTest {
.returns("EXPR$0=[250, 500, 1000]\n");
}
- @Ignore
@Test public void testJsonType() {
CalciteAssert.that()
.query("SELECT JSON_TYPE(v) AS c1\n"
@@ -6807,16 +6806,26 @@ public class JdbcTest {
.returns("C1=OBJECT; C2=ARRAY; C3=INTEGER; C4=BOOLEAN\n");
}
- @Ignore
@Test public void testJsonDepth() {
CalciteAssert.that()
- .query("SELECT JSON_DEPTH(v) AS c1\n"
- + ",JSON_DEPTH(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2\n"
- + ",JSON_DEPTH(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3\n"
- + ",JSON_DEPTH(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4\n"
- + "FROM (VALUES ('{\"a\": [10, true],\"b\": \"[10, true]\"}')) AS t(v)\n"
+ .query("SELECT JSON_DEPTH(v) AS c1\n"
+ + ",JSON_DEPTH(JSON_VALUE(v, 'lax $.b' ERROR ON ERROR)) AS c2\n"
+ + ",JSON_DEPTH(JSON_VALUE(v, 'strict $.a[0]' ERROR ON ERROR)) AS c3\n"
+ + ",JSON_DEPTH(JSON_VALUE(v, 'strict $.a[1]' ERROR ON ERROR)) AS c4\n"
+ + "FROM (VALUES ('{\"a\": [10, true],\"b\": \"[10, true]\"}')) AS t(v)\n"
+ + "limit 10")
+ .returns("C1=3; C2=2; C3=1; C4=1\n");
+ }
+
+ @Test public void testJsonLength() {
+ CalciteAssert.that()
+ .query("SELECT JSON_LENGTH(v) AS c1\n"
+ + ",JSON_LENGTH(v, 'lax $.a') AS c2\n"
+ + ",JSON_LENGTH(v, 'strict $.a[0]') AS c3\n"
+ + ",JSON_LENGTH(v, 'strict $.a[1]') AS c4\n"
+ + "FROM (VALUES ('{\"a\": [10, true]}')) AS t(v)\n"
+ "limit 10")
- .returns("C1=3; C2=2; C3=1; C4=1\n");
+ .returns("C1=1; C2=2; C3=1; C4=1\n");
}
@Test
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 3912a3d..9db52ee 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
@@ -495,6 +495,26 @@ public class SqlJsonFunctionsTest {
}
@Test
+ public void testJsonLength() {
+ assertJsonLength(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.LAX, Collections.singletonList("bar")),
+ is(1));
+ assertJsonLength(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.LAX, null),
+ nullValue());
+ assertJsonLength(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.STRICT, Collections.singletonList("bar")),
+ is(1));
+ assertJsonLength(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.LAX, "bar"),
+ is(1));
+ }
+
+ @Test
public void testJsonObjectAggAdd() {
Map<String, Object> map = new HashMap<>();
Map<String, Object> expected = new HashMap<>();
@@ -667,6 +687,22 @@ public class SqlJsonFunctionsTest {
matcher);
}
+ private void assertJsonLength(Object input,
+ Matcher<? super Integer> matcher) {
+ assertThat(
+ invocationDesc(BuiltInMethod.JSON_LENGTH.getMethodName(), input),
+ SqlFunctions.jsonLength(input),
+ matcher);
+ }
+
+ private void assertJsonLengthFailed(Object input,
+ Matcher<? super Throwable> matcher) {
+ assertFailed(
+ invocationDesc(BuiltInMethod.JSON_LENGTH.getMethodName(), input),
+ () -> SqlFunctions.jsonLength(input),
+ matcher);
+ }
+
private void assertDejsonize(String input,
Matcher<Object> matcher) {
assertThat(invocationDesc(BuiltInMethod.DEJSONIZE.getMethodName(), input),
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 d13788b..57776b2 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -8729,6 +8729,7 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
+ "DOT -\n"
+ "ITEM -\n"
+ "JSON_API_COMMON_SYNTAX -\n"
+ + "JSON_API_COMMON_SYNTAX_WITHOUT_PATH -\n"
+ "JSON_STRUCTURED_VALUE_EXPRESSION -\n"
+ "JSON_VALUE_EXPRESSION -\n"
+ "NEXT_VALUE -\n"
@@ -10815,6 +10816,14 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
"(.*)JSON_VALUE_EXPRESSION(.*)");
}
+ @Test public void testJsonLength() {
+ checkExp("json_length('{\"foo\":\"bar\"}')");
+ checkExp("json_length('{\"foo\":\"bar\"}', 'lax $')");
+ checkExpType("json_length('{\"foo\":\"bar\"}')", "INTEGER");
+ checkExpType("json_length('{\"foo\":\"bar\"}', 'lax $')", "INTEGER");
+ checkExpType("json_length('{\"foo\":\"bar\"}', 'strict $')", "INTEGER");
+ }
+
@Test public void testJsonObjectAgg() {
check("select json_objectagg(ename: empno) from emp");
checkFails("select ^json_objectagg(empno: ename)^ from emp",
diff --git a/server/src/main/codegen/config.fmpp b/server/src/main/codegen/config.fmpp
index 0a110fd..1a53d33 100644
--- a/server/src/main/codegen/config.fmpp
+++ b/server/src/main/codegen/config.fmpp
@@ -153,6 +153,7 @@ data: {
"JSON"
"JSON_TYPE"
"JSON_DEPTH"
+ "JSON_LENGTH"
"JSON_PRETTY"
"K"
"KEY"
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 591a54b..a322b48 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -566,6 +566,7 @@ JSON,
**JSON_ARRAYAGG**,
JSON_DEPTH,
**JSON_EXISTS**,
+JSON_LENGTH,
**JSON_OBJECT**,
**JSON_OBJECTAGG**,
JSON_PRETTY,
@@ -2007,6 +2008,7 @@ Note:
| JSON_TYPE(value) | Returns a string indicating the type of a JSON **value**. This can be an object, an array, or a scalar type
| JSON_DEPTH(value) | Returns a integer indicating the depth of a JSON **value**. This can be an object, an array, or a scalar type
| JSON_PRETTY(value) | Returns a pretty-printing of JSON **value**.
+| JSON_LENGTH(value) | Returns a integer indicating the length of a JSON **value**. This can be an object, an array, or a scalar type
* JSON_TYPE
@@ -2046,6 +2048,25 @@ Result:
| ------ | ----- | ------- | ------- |
| 3 | 2 | 1 | 1 |
+* JSON_LENGTH
+
+Example SQL:
+
+```SQL
+SELECT JSON_LENGTH(v) AS c1
+,JSON_LENGTH(v, 'lax $.a') AS c2
+,JSON_LENGTH(v, 'strict $.a[0]') AS c3
+,JSON_LENGTH(v, 'strict $.a[1]') AS c4
+FROM (VALUES ('{"a": [10, true]}')) AS t(v)
+LIMIT 10;
+```
+
+Result:
+
+| c1 | c2 | c3 | c4 |
+| ------ | ----- | ------- | ------- |
+| 1 | 2 | 1 | 1 |
+
## User-defined functions
Calcite is extensible. You can define each kind of function using user code.