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 <x1q...@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