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/04/03 02:47:42 UTC
[calcite] branch master updated: [CALCITE-2892] Add the JSON_KEYS
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 80dc5bc [CALCITE-2892] Add the JSON_KEYS function (xuqianjin)
80dc5bc is described below
commit 80dc5bcb3efa9c72573c4cc5b02f512d34c20d13
Author: XuQianJin-Stars <x1...@163.com>
AuthorDate: Thu Mar 7 00:34:42 2019 +0800
[CALCITE-2892] Add the JSON_KEYS function (xuqianjin)
Close apache/calcite#1086
---
babel/src/main/codegen/config.fmpp | 125 +++++++++++----------
core/src/main/codegen/config.fmpp | 3 +-
core/src/main/codegen/templates/Parser.jj | 30 ++++-
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../apache/calcite/runtime/CalciteResource.java | 3 +
.../org/apache/calcite/runtime/SqlFunctions.java | 29 +++++
.../calcite/sql/fun/SqlJsonDepthFunction.java | 6 -
...DepthFunction.java => SqlJsonKeysFunction.java} | 33 ++----
.../calcite/sql/fun/SqlJsonLengthFunction.java | 8 --
.../calcite/sql/fun/SqlJsonPrettyFunction.java | 6 -
.../calcite/sql/fun/SqlJsonTypeFunction.java | 6 -
.../calcite/sql/fun/SqlStdOperatorTable.java | 2 +
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/runtime/CalciteResource.properties | 1 +
core/src/test/codegen/config.fmpp | 3 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 31 +++--
.../apache/calcite/sql/parser/SqlParserTest.java | 9 ++
.../calcite/sql/test/SqlOperatorBaseTest.java | 50 +++++++++
.../java/org/apache/calcite/test/JdbcTest.java | 12 ++
.../apache/calcite/test/SqlJsonFunctionsTest.java | 56 +++++++--
.../org/apache/calcite/test/SqlValidatorTest.java | 6 +
server/src/main/codegen/config.fmpp | 3 +-
site/_docs/reference.md | 23 +++-
23 files changed, 307 insertions(+), 141 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp b/babel/src/main/codegen/config.fmpp
index 5439d14..cd5a36d 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -139,10 +139,11 @@ data: {
"ISOLATION"
"JAVA"
"JSON"
- "JSON_TYPE"
"JSON_DEPTH"
+ "JSON_KEYS"
"JSON_LENGTH"
"JSON_PRETTY"
+ "JSON_TYPE"
"K"
"KEY"
"KEY_MEMBER"
@@ -365,7 +366,7 @@ data: {
# "ANY",
"ARE",
"ARRAY",
-# # "ARRAY_AGG", # not a keyword in Calcite
+# "ARRAY_AGG", # not a keyword in Calcite
"ARRAY_MAX_CARDINALITY",
"AS",
"ASC",
@@ -384,7 +385,7 @@ data: {
"BIGINT",
"BINARY",
"BIT",
-# # "BIT_LENGTH", # not a keyword in Calcite
+# "BIT_LENGTH", # not a keyword in Calcite
"BLOB",
"BOOLEAN",
"BOTH",
@@ -449,12 +450,12 @@ data: {
"DATA",
# "DATE",
"DAY",
-# # "DAYS", # not a keyword in Calcite
+# "DAYS", # not a keyword in Calcite
"DEALLOCATE",
"DEC",
"DECIMAL",
"DECLARE",
-# # "DEFAULT",
+# "DEFAULT",
"DEFERRABLE",
"DEFERRED",
# "DEFINE",
@@ -464,42 +465,42 @@ data: {
"DEREF",
"DESC",
# "DESCRIBE", # must be reserved
- "DESCRIPTOR",
- "DETERMINISTIC",
- "DIAGNOSTICS",
- "DISALLOW",
- "DISCONNECT",
+ "DESCRIPTOR",
+ "DETERMINISTIC",
+ "DIAGNOSTICS",
+ "DISALLOW",
+ "DISCONNECT",
# "DISTINCT",
-# # "DO", # not a keyword in Calcite
- "DOMAIN",
- "DOUBLE",
+# "DO", # not a keyword in Calcite
+ "DOMAIN",
+ "DOUBLE",
# "DROP", # probably must be reserved
- "DYNAMIC",
- "EACH",
- "ELEMENT",
- "ELSE",
-# # "ELSEIF", # not a keyword in Calcite
- "EMPTY",
- "END",
-# # "END-EXEC", # not a keyword in Calcite, and contains '-'
- "END_FRAME",
- "END_PARTITION",
- "EQUALS",
- "ESCAPE",
- "EVERY",
+ "DYNAMIC",
+ "EACH",
+ "ELEMENT",
+ "ELSE",
+# "ELSEIF", # not a keyword in Calcite
+ "EMPTY",
+ "END",
+# "END-EXEC", # not a keyword in Calcite, and contains '-'
+ "END_FRAME",
+ "END_PARTITION",
+ "EQUALS",
+ "ESCAPE",
+ "EVERY",
# "EXCEPT", # must be reserved
- "EXCEPTION",
- "EXEC",
- "EXECUTE",
- "EXISTS",
-# # "EXIT", # not a keyword in Calcite
- "EXP",
+ "EXCEPTION",
+ "EXEC",
+ "EXECUTE",
+ "EXISTS",
+# "EXIT", # not a keyword in Calcite
+ "EXP",
# "EXPLAIN", # must be reserved
- "EXTEND",
- "EXTERNAL",
- "EXTRACT",
- "FALSE",
-# "FETCH",
+ "EXTEND",
+ "EXTERNAL",
+ "EXTRACT",
+ "FALSE",
+# "FETCH",
"FILTER",
"FIRST",
"FIRST_VALUE",
@@ -507,7 +508,7 @@ data: {
"FLOOR",
"FOR",
"FOREIGN",
-# # "FOREVER", # not a keyword in Calcite
+# "FOREVER", # not a keyword in Calcite
"FOUND",
"FRAME_ROW",
"FREE",
@@ -524,13 +525,13 @@ data: {
# "GROUP",
# "GROUPING",
"GROUPS",
-# # "HANDLER", # not a keyword in Calcite
+# "HANDLER", # not a keyword in Calcite
# "HAVING",
"HOLD",
"HOUR",
-# # "HOURS", # not a keyword in Calcite
+# "HOURS", # not a keyword in Calcite
"IDENTITY",
-# # "IF", # not a keyword in Calcite
+# "IF", # not a keyword in Calcite
"IMMEDIATE",
"IMMEDIATELY",
"IMPORT",
@@ -551,16 +552,20 @@ data: {
# "INTO",
"IS",
"ISOLATION",
-# # "ITERATE", # not a keyword in Calcite
+# "ITERATE", # not a keyword in Calcite
# "JOIN",
"JSON_ARRAY",
"JSON_ARRAYAGG",
+ "JSON_DEPTH",
"JSON_EXISTS",
- "JSON_VALUE",
+ "JSON_KEYS",
"JSON_OBJECT",
"JSON_OBJECTAGG",
+ "JSON_PRETTY",
"JSON_QUERY",
-# # "KEEP", # not a keyword in Calcite
+ "JSON_TYPE",
+ "JSON_VALUE",
+# "KEEP", # not a keyword in Calcite
"KEY",
"LAG",
"LANGUAGE",
@@ -570,7 +575,7 @@ data: {
# "LATERAL",
"LEAD",
"LEADING",
-# # "LEAVE", # not a keyword in Calcite
+# "LEAVE", # not a keyword in Calcite
# "LEFT",
"LEVEL",
"LIKE",
@@ -581,7 +586,7 @@ data: {
"LOCALTIME",
"LOCALTIMESTAMP",
"LOCATOR",
-# # "LOOP", # not a keyword in Calcite
+# "LOOP", # not a keyword in Calcite
"LOWER",
"MAP",
"MATCH",
@@ -589,7 +594,7 @@ data: {
"MATCH_NUMBER",
# "MATCH_RECOGNIZE",
"MAX",
-# # "MAX_CARDINALITY", # not a keyword in Calcite
+# "MAX_CARDINALITY", # not a keyword in Calcite
"MEASURES",
"MEMBER",
# "MERGE",
@@ -597,7 +602,7 @@ data: {
"MIN",
# "MINUS",
"MINUTE",
-# # "MINUTES", # not a keyword in Calcite
+# "MINUTES", # not a keyword in Calcite
"MOD",
"MODIFIES",
"MODULE",
@@ -687,9 +692,9 @@ data: {
"REGR_SYY",
"RELATIVE",
"RELEASE",
-# # "REPEAT", # not a keyword in Calcite
+# "REPEAT", # not a keyword in Calcite
"RESET",
-# # "RESIGNAL", # not a keyword in Calcite
+# "RESIGNAL", # not a keyword in Calcite
"RESTRICT",
"RESULT",
"RETURN",
@@ -710,7 +715,7 @@ data: {
"SCROLL",
"SEARCH",
"SECOND",
-# # "SECONDS", # not a keyword in Calcite
+# "SECONDS", # not a keyword in Calcite
"SECTION",
"SEEK",
# "SELECT",
@@ -720,18 +725,18 @@ data: {
# "SET",
# "SETS",
"SHOW",
-# # "SIGNAL", # not a keyword in Calcite
+# "SIGNAL", # not a keyword in Calcite
"SIMILAR",
"SIZE",
-# # "SKIP", # messes with JavaCC's <SKIP> token
+# "SKIP", # messes with JavaCC's <SKIP> token
"SMALLINT",
# "SOME",
"SPACE",
"SPECIFIC",
"SPECIFICTYPE",
"SQL",
-# # "SQLCODE", # not a keyword in Calcite
-# # "SQLERROR", # not a keyword in Calcite
+# "SQLCODE", # not a keyword in Calcite
+# "SQLERROR", # not a keyword in Calcite
"SQLEXCEPTION",
"SQLSTATE",
"SQLWARNING",
@@ -775,12 +780,12 @@ data: {
"TRUNCATE",
"UESCAPE",
"UNDER",
-# # "UNDO", # not a keyword in Calcite
+# "UNDO", # not a keyword in Calcite
# "UNION",
"UNIQUE",
"UNKNOWN",
# "UNNEST",
-# # "UNTIL", # not a keyword in Calcite
+# "UNTIL", # not a keyword in Calcite
# "UPDATE",
"UPPER",
"UPSERT",
@@ -797,12 +802,12 @@ data: {
"VAR_SAMP",
"VERSION",
"VERSIONING",
-# # "VERSIONS", # not a keyword in Calcite
+# "VERSIONS", # not a keyword in Calcite
"VIEW",
# "WHEN",
"WHENEVER",
# "WHERE",
-# # "WHILE", # not a keyword in Calcite
+# "WHILE", # not a keyword in Calcite
"WIDTH_BUCKET",
# "WINDOW",
# "WITH",
@@ -811,7 +816,7 @@ data: {
"WORK",
"WRITE",
"YEAR",
- # "YEARS", # not a keyword in Calcite
+# "YEARS", # not a keyword in Calcite
"ZONE",
]
diff --git a/core/src/main/codegen/config.fmpp b/core/src/main/codegen/config.fmpp
index 7f55730..0138304 100644
--- a/core/src/main/codegen/config.fmpp
+++ b/core/src/main/codegen/config.fmpp
@@ -160,10 +160,11 @@ data: {
"ISOLATION"
"JAVA"
"JSON"
- "JSON_TYPE"
"JSON_DEPTH"
+ "JSON_KEYS"
"JSON_LENGTH"
"JSON_PRETTY"
+ "JSON_TYPE"
"K"
"KEY"
"KEY_MEMBER"
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 22e1641..9960fd1 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -4854,6 +4854,8 @@ SqlNode BuiltinFunctionCall() :
|
node = JsonValueFunctionCall() { return node; }
|
+ node = JsonKeysFunctionCall() { return node; }
+ |
node = JsonPrettyFunctionCall() { return node; }
|
node = JsonQueryFunctionCall() { return node; }
@@ -5117,6 +5119,23 @@ SqlCall JsonValueFunctionCall() :
}
}
+SqlCall JsonKeysFunctionCall() :
+{
+ final SqlNode[] args = new SqlNode[1];
+ SqlNode e;
+ final Span span;
+ List<SqlNode> behavior;
+}
+{
+ <JSON_KEYS> { span = span(); }
+ <LPAREN> e = JsonApiCommonSyntax(true) {
+ args[0] = e;
+ }
+ <RPAREN> {
+ return SqlStdOperatorTable.JSON_KEYS.createCall(span.end(this), args);
+ }
+}
+
SqlCall JsonPrettyFunctionCall() :
{
final SqlNode[] args = new SqlNode[1];
@@ -6453,15 +6472,16 @@ SqlPostfixOperator PostfixRowOperator() :
| < JSON: "JSON" >
| < JSON_ARRAY: "JSON_ARRAY">
| < JSON_ARRAYAGG: "JSON_ARRAYAGG">
-| < JSON_EXISTS: "JSON_EXISTS" >
-| < JSON_VALUE: "JSON_VALUE" >
-| < JSON_PRETTY: "JSON_PRETTY" >
-| < JSON_OBJECT: "JSON_OBJECT">
-| < JSON_TYPE: "JSON_TYPE">
| < JSON_DEPTH: "JSON_DEPTH">
+| < JSON_EXISTS: "JSON_EXISTS" >
+| < JSON_KEYS: "JSON_KEYS" >
| < JSON_LENGTH: "JSON_LENGTH">
+| < JSON_OBJECT: "JSON_OBJECT">
| < JSON_OBJECTAGG: "JSON_OBJECTAGG">
+| < JSON_PRETTY: "JSON_PRETTY" >
| < JSON_QUERY: "JSON_QUERY" >
+| < JSON_TYPE: "JSON_TYPE">
+| < JSON_VALUE: "JSON_VALUE" >
| < K: "K" >
| < KEY: "KEY" >
| < KEY_MEMBER: "KEY_MEMBER" >
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 298a5c0..fd535ca 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
@@ -167,6 +167,7 @@ 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_KEYS;
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;
@@ -466,6 +467,7 @@ public class RexImpTable {
defineMethod(JSON_ARRAY, BuiltInMethod.JSON_ARRAY.method, NullPolicy.NONE);
defineMethod(JSON_TYPE, BuiltInMethod.JSON_TYPE.method, NullPolicy.NONE);
defineMethod(JSON_DEPTH, BuiltInMethod.JSON_DEPTH.method, NullPolicy.NONE);
+ defineMethod(JSON_KEYS, BuiltInMethod.JSON_KEYS.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),
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 66244ee..3d12aee 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -879,6 +879,9 @@ public interface CalciteResource {
@BaseMessage("Not a valid input for JSON_LENGTH: ''{0}''")
ExInst<CalciteException> invalidInputForJsonLength(String value);
+
+ @BaseMessage("Not a valid input for JSON_KEYS: ''{0}''")
+ ExInst<CalciteException> invalidInputForJsonKeys(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 2ab9a3a..2298661 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -2840,6 +2840,35 @@ public class SqlFunctions {
return result;
}
+ public static String jsonKeys(Object input) {
+ List<String> list = new ArrayList<>();
+ 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) || (value instanceof Collection)
+ || isScalarObject(value)) {
+ list = null;
+ } else if (value instanceof Map) {
+ for (Object key : ((LinkedHashMap) value).keySet()) {
+ list.add(key.toString());
+ }
+ }
+ } catch (Exception ex) {
+ throw RESOURCE.invalidInputForJsonKeys(
+ input.toString()).ex();
+ }
+ return jsonize(list);
+ }
+
public static boolean isJsonPathContext(Object input) {
try {
PathContext context = (PathContext) input;
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java
index 6b7e59b..6137096 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java
@@ -23,7 +23,6 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperandCountRange;
-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;
@@ -57,11 +56,6 @@ public class SqlJsonDepthFunction extends SqlFunction {
SqlParserPos pos, SqlNode... operands) {
return super.createCall(functionQualifier, pos, operands);
}
-
- @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
- int rightPrec) {
- super.unparse(writer, call, 0, 0);
- }
}
// End SqlJsonDepthFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonKeysFunction.java
similarity index 67%
copy from core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java
copy to core/src/main/java/org/apache/calcite/sql/fun/SqlJsonKeysFunction.java
index 6b7e59b..8cd6444 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonDepthFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonKeysFunction.java
@@ -22,30 +22,22 @@ 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.SqlOperandCountRange;
-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.SqlOperandCountRanges;
import org.apache.calcite.sql.type.SqlOperandTypeChecker;
import org.apache.calcite.sql.validate.SqlValidator;
/**
- * The <code>JSON_DEPTH</code> function.
+ * The <code>JSON_KEYS</code> function.
*/
-public class SqlJsonDepthFunction extends SqlFunction {
- public SqlJsonDepthFunction() {
- super("JSON_DEPTH",
- SqlKind.OTHER_FUNCTION,
- ReturnTypes.INTEGER_NULLABLE,
- null,
- OperandTypes.ANY,
- SqlFunctionCategory.SYSTEM);
- }
-
- @Override public SqlOperandCountRange getOperandCountRange() {
- return SqlOperandCountRanges.of(1);
+public class SqlJsonKeysFunction extends SqlFunction {
+ public SqlJsonKeysFunction() {
+ super("JSON_KEYS", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.VARCHAR_2000,
+ null,
+ OperandTypes.ANY,
+ SqlFunctionCategory.SYSTEM);
}
@Override protected void checkOperandCount(SqlValidator validator,
@@ -54,14 +46,9 @@ public class SqlJsonDepthFunction extends SqlFunction {
}
@Override public SqlCall createCall(SqlLiteral functionQualifier,
- SqlParserPos pos, SqlNode... operands) {
+ SqlParserPos pos, SqlNode... operands) {
return super.createCall(functionQualifier, pos, operands);
}
-
- @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
- int rightPrec) {
- super.unparse(writer, call, 0, 0);
- }
}
-// End SqlJsonDepthFunction.java
+// End SqlJsonKeysFunction.java
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
index 8140efd..4b9308a 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonLengthFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonLengthFunction.java
@@ -41,14 +41,6 @@ public class SqlJsonLengthFunction extends SqlFunction {
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());
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java
index cc33d31..5826eaa 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonPrettyFunction.java
@@ -23,7 +23,6 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperandCountRange;
-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;
@@ -58,11 +57,6 @@ public class SqlJsonPrettyFunction extends SqlFunction {
SqlParserPos pos, SqlNode... operands) {
return super.createCall(functionQualifier, pos, operands);
}
-
- @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
- int rightPrec) {
- super.unparse(writer, call, 0, 0);
- }
}
// End SqlJsonPrettyFunction.java
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java
index 7917c58..1d03378 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlJsonTypeFunction.java
@@ -23,7 +23,6 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlOperandCountRange;
-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;
@@ -58,11 +57,6 @@ public class SqlJsonTypeFunction extends SqlFunction {
SqlParserPos pos, SqlNode... operands) {
return super.createCall(functionQualifier, pos, operands);
}
-
- @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
- int rightPrec) {
- super.unparse(writer, call, 0, 0);
- }
}
// End SqlJsonTypeFunction.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 3d57072..0e9d563 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
@@ -1309,6 +1309,8 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
public static final SqlFunction JSON_VALUE =
new SqlJsonValueFunction("JSON_VALUE", false);
+ public static final SqlFunction JSON_KEYS = new SqlJsonKeysFunction();
+
public static final SqlFunction JSON_PRETTY =
new SqlJsonPrettyFunction();
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 e0add52..9d2de30 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -289,6 +289,7 @@ public enum BuiltInMethod {
SqlJsonConstructorNullClause.class),
JSON_TYPE(SqlFunctions.class, "jsonType", Object.class),
JSON_DEPTH(SqlFunctions.class, "jsonDepth", Object.class),
+ JSON_KEYS(SqlFunctions.class, "jsonKeys", Object.class),
JSON_PRETTY(SqlFunctions.class, "jsonPretty", Object.class),
JSON_LENGTH(SqlFunctions.class, "jsonLength", Object.class),
JSON_OBJECTAGG_ADD(SqlFunctions.class, "jsonObjectAggAdd", Map.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 2afd902..d9ebc4b 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -286,4 +286,5 @@ 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}''
+InvalidInputForJsonKeys=Not a valid input for JSON_KEYS: ''{0}''
# End CalciteResource.properties
diff --git a/core/src/test/codegen/config.fmpp b/core/src/test/codegen/config.fmpp
index 79e0315..230b280 100644
--- a/core/src/test/codegen/config.fmpp
+++ b/core/src/test/codegen/config.fmpp
@@ -143,10 +143,11 @@ data: {
"ISOLATION"
"JAVA"
"JSON"
- "JSON_TYPE"
"JSON_DEPTH"
+ "JSON_KEYS"
"JSON_LENGTH"
"JSON_PRETTY"
+ "JSON_TYPE"
"K"
"KEY"
"KEY_MEMBER"
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 a6176fb..d391a2e 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
@@ -3127,7 +3127,7 @@ public class RelToSqlConverterTest {
@Test public void testJsonPretty() {
String query = "select json_pretty(\"product_name\") from \"product\"";
final String expected = "SELECT JSON_PRETTY(\"product_name\" FORMAT JSON)\n"
- + "FROM \"foodmart\".\"product\"";
+ + "FROM \"foodmart\".\"product\"";
sql(query).ok(expected);
}
@@ -3217,25 +3217,32 @@ public class RelToSqlConverterTest {
@Test public void testJsonType() {
String query = "select json_type(\"product_name\") from \"product\"";
final String expected = "SELECT "
- + "JSON_TYPE(\"product_name\" FORMAT JSON)\n"
- + "FROM \"foodmart\".\"product\"";
+ + "JSON_TYPE(\"product_name\" FORMAT JSON)\n"
+ + "FROM \"foodmart\".\"product\"";
sql(query).ok(expected);
}
@Test public void testJsonDepth() {
String query = "select json_depth(\"product_name\") from \"product\"";
final String expected = "SELECT "
- + "JSON_DEPTH(\"product_name\" FORMAT JSON)\n"
- + "FROM \"foodmart\".\"product\"";
+ + "JSON_DEPTH(\"product_name\" FORMAT JSON)\n"
+ + "FROM \"foodmart\".\"product\"";
sql(query).ok(expected);
}
@Test public void testJsonLength() {
String query = "select json_length(\"product_name\", 'lax $'), "
- + "json_length(\"product_name\") from \"product\"";
+ + "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\"";
+ + "JSON_LENGTH(\"product_name\" FORMAT JSON)\n"
+ + "FROM \"foodmart\".\"product\"";
+ sql(query).ok(expected);
+ }
+
+ @Test public void testJsonKeys() {
+ String query = "select json_keys(\"product_name\", 'lax $') from \"product\"";
+ final String expected = "SELECT JSON_KEYS(\"product_name\" FORMAT JSON, 'lax $')\n"
+ + "FROM \"foodmart\".\"product\"";
sql(query).ok(expected);
}
@@ -3332,10 +3339,10 @@ public class RelToSqlConverterTest {
.withDataTypeSystem(new RelDataTypeSystemImpl() {
@Override public int getMaxPrecision(SqlTypeName typeName) {
switch (typeName) {
- case VARCHAR:
- return 256;
- default:
- return super.getMaxPrecision(typeName);
+ case VARCHAR:
+ return 256;
+ default:
+ return super.getMaxPrecision(typeName);
}
}
}));
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 c9ce179..62a3798 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
@@ -8472,6 +8472,15 @@ public class SqlParserTest {
"JSON_LENGTH('{\"foo\": \"bar\"}' FORMAT JSON, 'invalid $')");
}
+ @Test public void testJsonKeys() {
+ checkExp("json_keys('{\"foo\": \"bar\"}', 'lax $')",
+ "JSON_KEYS('{\"foo\": \"bar\"}' FORMAT JSON, 'lax $')");
+ checkExp("json_keys('{\"foo\": \"bar\"}', 'strict $')",
+ "JSON_KEYS('{\"foo\": \"bar\"}' FORMAT JSON, 'strict $')");
+ checkExp("json_keys('{\"foo\": \"bar\"}', 'invalid $')",
+ "JSON_KEYS('{\"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 f078920..4f49fb8 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
@@ -4593,6 +4593,56 @@ public abstract class SqlOperatorBaseTest {
"(?s).*No results for path.*", true);
}
+ @Test public void testJsonKeys() {
+ // no path context
+ tester.checkString("json_keys('{}')",
+ "[]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('[]')",
+ "null", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"foo\":100}')",
+ "[\"foo\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"a\": 1, \"b\": {\"c\": 30}}')",
+ "[\"a\",\"b\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('[1, 2, {\"a\": 3}]')",
+ "null", "VARCHAR(2000) NOT NULL");
+
+ // lax test
+ tester.checkString("json_keys('{}', 'lax $')",
+ "[]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('[]', 'lax $')",
+ "null", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"foo\":100}', 'lax $')",
+ "[\"foo\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"a\": 1, \"b\": {\"c\": 30}}', 'lax $')",
+ "[\"a\",\"b\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('[1, 2, {\"a\": 3}]', 'lax $')",
+ "null", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"a\": 1, \"b\": {\"c\": 30}}', 'lax $.b')",
+ "[\"c\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"foo\":100}', 'lax $.foo1')",
+ "null", "VARCHAR(2000) NOT NULL");
+
+ // strict test
+ tester.checkString("json_keys('{}', 'strict $')",
+ "[]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('[]', 'strict $')",
+ "null", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"foo\":100}', 'strict $')",
+ "[\"foo\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"a\": 1, \"b\": {\"c\": 30}}', 'strict $')",
+ "[\"a\",\"b\"]", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('[1, 2, {\"a\": 3}]', 'strict $')",
+ "null", "VARCHAR(2000) NOT NULL");
+ tester.checkString("json_keys('{\"a\": 1, \"b\": {\"c\": 30}}', 'strict $.b')",
+ "[\"c\"]", "VARCHAR(2000) NOT NULL");
+
+ // catch error test
+ tester.checkFails("json_keys('{\"foo\":100}', 'invalid $.foo')",
+ "(?s).*Illegal jsonpath spec.*", true);
+ tester.checkFails("json_keys('{\"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 e4f711a..79eef06 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -6864,6 +6864,18 @@ public class JdbcTest {
+ "}\n");
}
+ @Test public void testJsonKeys() {
+ CalciteAssert.that()
+ .query("SELECT JSON_KEYS(v) AS c1\n"
+ + ",JSON_KEYS(v, 'lax $.a') AS c2\n"
+ + ",JSON_KEYS(v, 'lax $.b') AS c3\n"
+ + ",JSON_KEYS(v, 'strict $.a[0]') AS c4\n"
+ + ",JSON_KEYS(v, 'strict $.a[1]') AS c5\n"
+ + "FROM (VALUES ('{\"a\": [10, true],\"b\": {\"c\": 30}}')) AS t(v)\n"
+ + "limit 10")
+ .returns("C1=[\"a\",\"b\"]; C2=null; C3=[\"c\"]; C4=null; C5=null\n");
+ }
+
/**
* Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-2609">[CALCITE-2609]
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 9db52ee..5d9ca43 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlJsonFunctionsTest.java
@@ -474,7 +474,7 @@ public class SqlJsonFunctionsTest {
public void testJsonType() {
assertJsonType(is("OBJECT"), "{}");
assertJsonType(is("ARRAY"),
- "[\"foo\",null]");
+ "[\"foo\",null]");
assertJsonType(is("NULL"), "null");
assertJsonType(is("BOOLEAN"), "false");
assertJsonType(is("INTEGER"), "12");
@@ -488,9 +488,9 @@ public class SqlJsonFunctionsTest {
assertJsonDepth(is(1), "12");
assertJsonDepth(is(1), "11.22");
assertJsonDepth(is(2),
- "[\"foo\",null]");
+ "[\"foo\",null]");
assertJsonDepth(is(3),
- "{\"a\": [10, true]}");
+ "{\"a\": [10, true]}");
assertJsonDepth(nullValue(), "null");
}
@@ -514,6 +514,25 @@ public class SqlJsonFunctionsTest {
is(1));
}
+ public void testJsonKeys() {
+ assertJsonKeys(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.LAX, Collections.singletonList("bar")),
+ is("null"));
+ assertJsonKeys(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.LAX, null),
+ is("null"));
+ assertJsonKeys(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.STRICT, Collections.singletonList("bar")),
+ is("null"));
+ assertJsonKeys(
+ SqlFunctions.PathContext
+ .withReturned(SqlFunctions.PathMode.LAX, "bar"),
+ is("null"));
+ }
+
@Test
public void testJsonObjectAggAdd() {
Map<String, Object> map = new HashMap<>();
@@ -703,10 +722,25 @@ public class SqlJsonFunctionsTest {
matcher);
}
+ private void assertJsonKeys(Object input,
+ Matcher<? super String> matcher) {
+ assertThat(
+ invocationDesc(BuiltInMethod.JSON_KEYS.getMethodName(), input),
+ SqlFunctions.jsonKeys(input),
+ matcher);
+ }
+
+ private void assertJsonKeysFailed(Object input,
+ Matcher<? super Throwable> matcher) {
+ assertFailed(invocationDesc(BuiltInMethod.JSON_KEYS.getMethodName(), input),
+ () -> SqlFunctions.jsonKeys(input),
+ matcher);
+ }
+
private void assertDejsonize(String input,
Matcher<Object> matcher) {
assertThat(invocationDesc(BuiltInMethod.DEJSONIZE.getMethodName(), input),
- SqlFunctions.dejsonize(input),
+ SqlFunctions.dejsonize(input),
matcher);
}
@@ -726,21 +760,21 @@ public class SqlJsonFunctionsTest {
}
private void assertJsonType(Matcher<? super String> matcher,
- String input) {
+ String input) {
assertThat(
invocationDesc(BuiltInMethod.JSON_TYPE.getMethodName(), input),
SqlFunctions.jsonType(
- SqlFunctions.dejsonize(input)),
+ SqlFunctions.dejsonize(input)),
matcher);
}
private void assertJsonDepth(Matcher<? super Integer> matcher,
- String input) {
+ String input) {
assertThat(
- invocationDesc(BuiltInMethod.JSON_DEPTH.getMethodName(), input),
- SqlFunctions.jsonDepth(
- SqlFunctions.dejsonize(input)),
- matcher);
+ invocationDesc(BuiltInMethod.JSON_DEPTH.getMethodName(), input),
+ SqlFunctions.jsonDepth(
+ SqlFunctions.dejsonize(input)),
+ matcher);
}
private void assertJsonObjectAggAdd(Map map, String k, Object v,
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 e7f3691..89e0f5c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -10962,6 +10962,12 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
checkExpType("json_length('{\"foo\":\"bar\"}', 'strict $')", "INTEGER");
}
+ @Test public void testJsonKeys() {
+ checkExp("json_keys('{\"foo\":\"bar\"}', 'lax $')");
+ checkExpType("json_keys('{\"foo\":\"bar\"}', 'lax $')", "VARCHAR(2000) NOT NULL");
+ checkExpType("json_keys('{\"foo\":\"bar\"}', 'strict $')", "VARCHAR(2000) NOT NULL");
+ }
+
@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 1a53d33..821e752 100644
--- a/server/src/main/codegen/config.fmpp
+++ b/server/src/main/codegen/config.fmpp
@@ -151,10 +151,11 @@ data: {
"ISOLATION"
"JAVA"
"JSON"
- "JSON_TYPE"
"JSON_DEPTH"
+ "JSON_KEYS"
"JSON_LENGTH"
"JSON_PRETTY"
+ "JSON_TYPE"
"K"
"KEY"
"KEY_MEMBER"
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 731cb46..62a2108 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -567,6 +567,7 @@ JSON,
**JSON_ARRAYAGG**,
JSON_DEPTH,
**JSON_EXISTS**,
+JSON_KEYS,
JSON_LENGTH,
**JSON_OBJECT**,
**JSON_OBJECTAGG**,
@@ -2052,6 +2053,7 @@ Note:
| JSON_DEPTH(jsonValue) | Returns an integer value indicating the depth of a *jsonValue*
| JSON_PRETTY(jsonValue) | Returns a pretty-printing of *jsonValue*
| JSON_LENGTH(jsonValue [, path ]) | Returns a integer indicating the length of *jsonValue*
+| JSON_KEYS(jsonValue [, path ]) | Returns a string indicating the keys of a JSON *jsonValue*
Note:
@@ -2135,13 +2137,32 @@ Result
| ------ | ----- | ------- | ------- |
| 1 | 2 | 1 | 1 |
+##### JSON_KEYS example
+
+SQL
+
+ ```SQL
+SELECT JSON_KEYS(v) AS c1
+,JSON_KEYS(v, 'lax $.a') AS c2
+,JSON_KEYS(v, 'lax $.b') AS c2
+,JSON_KEYS(v, 'strict $.a[0]') AS c3
+,JSON_KEYS(v, 'strict $.a[1]') AS c4
+FROM (VALUES ('{"a": [10, true],"b": {"c": 30}}')) AS t(v)
+LIMIT 10;
+```
+
+ Result
+
+| c1 | c2 | c3 | c4 | c5 |
+| ---------- | ---- | ----- | ---- | ---- |
+| ["a", "b"] | NULL | ["c"] | NULL | NULL |
+
Not implemented:
* JSON_INSERT
* JSON_SET
* JSON_REPLACE
* JSON_REMOVE
-* JSON_KEYS
## User-defined functions