This is an automated email from the ASF dual-hosted git repository.
eldenmoon pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 3c1d7d27269 [improve](functon) improve json_object with complex type
(#47627)
3c1d7d27269 is described below
commit 3c1d7d27269cab0022bc5b293da45dbb6013dff0
Author: amory <[email protected]>
AuthorDate: Tue Feb 11 21:09:28 2025 +0800
[improve](functon) improve json_object with complex type (#47627)
This PR enhances the value parameter of the json_object function as a
complex type which refer to postgresql
https://www.postgresql.org/docs/current/functions-json.html
```
json_object ( [ { key_expression { VALUE | ':' } value_expression [ FORMAT
JSON [ ENCODING UTF8 ] ] }[, ...] ] [ { NULL | ABSENT } ON NULL ] [ { WITH |
WITHOUT } UNIQUE [ KEYS ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8
] ] ])
Constructs a JSON object of all the key/value pairs given, or an empty
object if none are given. key_expression is a scalar expression defining the
JSON key, which is converted to the text type. It cannot be NULL nor can it
belong to a type that has a cast to the json type. If WITH UNIQUE KEYS is
specified, there must not be any duplicate key_expression. Any pair for which
the value_expression evaluates to NULL is omitted from the output if ABSENT ON
NULL is specified; if NULL ON NULL [...]
```
---
be/src/vec/functions/function_json.cpp | 17 ++++++-
.../java/org/apache/doris/analysis/CastExpr.java | 4 ++
.../apache/doris/analysis/FunctionCallExpr.java | 3 ++
.../expressions/functions/scalar/JsonObject.java | 36 +++++++------
.../query_p0/cast/test_complextype_to_json.out | Bin 2450 -> 2124 bytes
.../json_function/test_query_json_object.out | Bin 524 -> 1959 bytes
.../json_function/test_query_json_object.groovy | 56 +++++++++++++++++++++
7 files changed, 100 insertions(+), 16 deletions(-)
diff --git a/be/src/vec/functions/function_json.cpp
b/be/src/vec/functions/function_json.cpp
index c53b31d7ec6..a299f547660 100644
--- a/be/src/vec/functions/function_json.cpp
+++ b/be/src/vec/functions/function_json.cpp
@@ -571,7 +571,7 @@ struct JsonParser<'1'> {
StringRef data,
rapidjson::Document::AllocatorType& allocator) {
DCHECK(data.size == 1 || strncmp(data.data, "true", 4) == 0 ||
strncmp(data.data, "false", 5) == 0);
- value.SetBool((*data.data == '1' || *data.data == 't') ? true : false);
+ value.SetBool(*data.data == '1' || *data.data == 't');
}
};
@@ -612,6 +612,18 @@ struct JsonParser<'5'> {
}
};
+template <>
+struct JsonParser<'7'> {
+ // json string
+ static void update_value(StringParser::ParseResult& result,
rapidjson::Value& value,
+ StringRef data,
rapidjson::Document::AllocatorType& allocator) {
+ rapidjson::Document document;
+ JsonbValue* json_val = JsonbDocument::createValue(data.data,
data.size);
+ convert_jsonb_to_rapidjson(*json_val, document, allocator);
+ value.CopyFrom(document, allocator);
+ }
+};
+
template <int flag, typename Impl>
struct ExecuteReducer {
template <typename... TArgs>
@@ -673,7 +685,8 @@ struct FunctionJsonObjectImpl {
}
for (int i = 0; i + 1 < data_columns.size() - 1; i += 2) {
- constexpr_int_match<'0', '6', Reducer>::run(type_flags[i + 1],
objects, allocator,
+ // last is for old type definition
+ constexpr_int_match<'0', '7', Reducer>::run(type_flags[i + 1],
objects, allocator,
data_columns[i],
data_columns[i + 1],
nullmaps[i + 1]);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
index de257991ca6..75bc129b523 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/CastExpr.java
@@ -335,6 +335,10 @@ public class CastExpr extends Expr {
if ((type.isMapType() || type.isStructType()) &&
childType.isStringType()) {
return;
}
+ // same with Type.canCastTo() can be cast to jsonb
+ if (childType.isComplexType() && type.isJsonbType()) {
+ return;
+ }
if (childType.isNull() && Type.canCastTo(childType, type)) {
return;
} else {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
index 51e18d750f2..e2f945c6941 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java
@@ -490,7 +490,10 @@ public class FunctionCallExpr extends Expr {
return 3;
} else if (type.isTime()) {
return 4;
+ } else if (type.isComplexType() || type.isJsonbType()) {
+ return 7;
} else {
+ // default is string for BE execution
return 6;
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonObject.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonObject.java
index 6d3d1536eb8..fabe8acc65f 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonObject.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonObject.java
@@ -21,24 +21,23 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNotNullable;
-import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.functions.CustomSignature;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.JsonType;
import org.apache.doris.nereids.types.VarcharType;
import org.apache.doris.nereids.util.ExpressionUtils;
-import com.google.common.collect.ImmutableList;
-
+import java.util.ArrayList;
import java.util.List;
/**
* ScalarFunction 'json_object'. This class is generated by GenerateFunction.
+ * Builds a JSON object out of a variadic argument list.
+ * By convention, the argument list consists of alternating keys and values.
+ * Key arguments are coerced to text; value arguments are converted as per
to_json or to_jsonb.
*/
-public class JsonObject extends ScalarFunction
- implements ExplicitlyCastableSignature, AlwaysNotNullable {
-
- public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT).varArgs(VarcharType.SYSTEM_DEFAULT)
- );
+public class JsonObject extends ScalarFunction implements CustomSignature,
AlwaysNotNullable {
/**
* constructor with 0 or more arguments.
@@ -47,6 +46,20 @@ public class JsonObject extends ScalarFunction
super("json_object", ExpressionUtils.mergeArguments(varArgs));
}
+ @Override
+ public FunctionSignature customSignature() {
+ List<DataType> arguments = new ArrayList<>();
+ for (int i = 0; i < arity(); i++) {
+ if ((i & 1) == 1 && (getArgumentType(i).isComplexType() ||
getArgumentType(i).isJsonType())) {
+ // keep origin type for BE Serialization
+ arguments.add(JsonType.INSTANCE);
+ } else {
+ arguments.add(VarcharType.SYSTEM_DEFAULT);
+ }
+ }
+ return FunctionSignature.of(VarcharType.SYSTEM_DEFAULT, arguments);
+ }
+
@Override
public void checkLegalityBeforeTypeCoercion() {
if ((arity() & 1) == 1) {
@@ -67,11 +80,6 @@ public class JsonObject extends ScalarFunction
return new JsonObject(children.toArray(new Expression[0]));
}
- @Override
- public List<FunctionSignature> getSignatures() {
- return SIGNATURES;
- }
-
@Override
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitJsonObject(this, context);
diff --git a/regression-test/data/query_p0/cast/test_complextype_to_json.out
b/regression-test/data/query_p0/cast/test_complextype_to_json.out
index 7412966f767..c209c387923 100644
Binary files a/regression-test/data/query_p0/cast/test_complextype_to_json.out
and b/regression-test/data/query_p0/cast/test_complextype_to_json.out differ
diff --git
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_object.out
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_object.out
index 13d30f6e75e..f4f51b3d69b 100644
Binary files
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_object.out
and
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_object.out
differ
diff --git
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_object.groovy
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_object.groovy
index 2ee0c64276c..60c08780c7c 100644
---
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_object.groovy
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_object.groovy
@@ -48,4 +48,60 @@ suite("test_query_json_object", "query") {
qt_sql2 """select json_object ( CONCAT('k',t.number%30926%3000 +
0),CONCAT('k',t.number%30926%3000 + 0,t.number%1000000) ) from numbers("number"
= "2") t order by 1;"""
sql "DROP TABLE ${tableName};"
+
+ // test json_object with complex type
+ // literal cases
+ // array
+ qt_sql_array """ SELECT json_object('id', 1, 'level',
array('"aaa"','"bbb"')); """
+ qt_sql_array """ SELECT json_object('id', 1, 'level', array('aaa','bbb'));
"""
+ qt_sql_array """ SELECT json_object('id', 1, 'level', array(1,2)); """
+ qt_sql_array """ SELECT json_object('id', 1, 'level', array(1.1,2.2)); """
+ qt_sql_array """ SELECT json_object('id', 1, 'level', array(1.1,2)); """
+ qt_sql_array """ SELECT json_object('id', 1, 'level', array(cast(1 as
decimal), cast(1.2 as decimal))); """
+ // map
+ qt_sql_map """ SELECT json_object('id', 1, 'level', map('a', 'b', 'c',
'd')); """
+ qt_sql_map """ SELECT json_object('id', 1, 'level', map('a', 1, 'c', 2));
"""
+ qt_sql_map """ SELECT json_object('id', 1, 'level', map('a', 1.1, 'c',
2.2)); """
+ qt_sql_map """ SELECT json_object('id', 1, 'level', map('a', 1.1, 'c',
2)); """
+ qt_sql_map """ SELECT json_object('id', 1, 'level', map('a', cast(1 as
decimal), 'c', cast(1.2 as decimal))); """
+ // struct
+ qt_sql_struct """ SELECT json_object('id', 1, 'level',
named_struct('name', 'a', 'age', 1)); """
+ qt_sql_struct """ SELECT json_object('id', 1, 'level',
named_struct('name', 'a', 'age', 1.1)); """
+ qt_sql_struct """ SELECT json_object('id', 1, 'level',
named_struct('name', 'a', 'age', 1)); """
+ qt_sql_struct """ SELECT json_object('id', 1, 'level',
named_struct('name', 'a', 'age', 1.1)); """
+ // json
+ qt_sql_json """ SELECT json_object('id', 1, 'level', cast('{\"a\":\"b\"}'
as JSON)); """
+ qt_sql_json """ SELECT json_object('id', 1, 'level', cast('{\"a\":1}' as
JSON)); """
+ qt_sql_json """ SELECT json_object('id', 1, 'level', cast('{\"a\":1.1}' as
JSON)); """
+ qt_sql_json """ SELECT json_object('id', 1, 'level', cast('{\"a\":1.1}' as
JSON)); """
+ qt_sql_json """ SELECT json_object('id', 1, 'level', cast('{\"a\":1.1}' as
JSON)); """
+
+
+
+ tableName = "test_query_json_object_complex"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE test_query_json_object_complex (
+ `k0` int(11) not null,
+ `k1` array<string> NULL,
+ `k2` map<string, string> NULL,
+ `k3` struct<name:string, age:int> NULL,
+ `k4` json NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`k0`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`k0`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+ sql "insert into ${tableName} values(1,null,null,null,null);"
+ sql "insert into ${tableName} values(2, array('a','b'), map('a','b'),
named_struct('name','a','age',1), '{\"a\":\"b\"}');"
+ sql """insert into ${tableName} values(3, array('"a"', '"b"'), map('"a"',
'"b"', '"c"', '"d"'), named_struct('name','"a"','age', 1), '{\"c\":\"d\"}');"""
+ sql """insert into ${tableName} values(4, array(1,2), map(1,2),
named_struct('name', 2, 'age',1), '{\"a\":\"b\"}');"""
+ sql """insert into ${tableName} values(5, array(1,2,3,3), map(1,2,3,4),
named_struct('name',\"a\",'age',1), '{\"a\":\"b\"}');"""
+ qt_sql2 "select json_object('k0',k0,'k1',k1,'k2',k2,'k3',k3,'k4',k4) from
${tableName} order by k0;"
+
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
