This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 195cb3184eb [fix](json-functions)fix json-replace/insert/set/array
behavior with … (#50890)
195cb3184eb is described below
commit 195cb3184eb7d0399ebb0000df7b5c7e46a42943
Author: amory <[email protected]>
AuthorDate: Wed May 14 21:54:50 2025 +0800
[fix](json-functions)fix json-replace/insert/set/array behavior with …
(#50890)
…complex type (#50308)
here are some functions behavior is not same with mysql as JSON_REPLACE
| JSON_INSERT | JSON_SET | JSON_ARRAY
which json and complex types are not supported before this PR Follow
mysql :
https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html#function_json-set
before we will meet result:
```
mysql> insert into t01 values (1, '{"a":1, "b":2}');
Query OK, 1 row affected (0.25 sec)
{'label':'label_1056456f8d8c46e5_9be38bb344bdd72c', 'status':'VISIBLE',
'txnId':'8019'}
mysql> select b, json_set(b, "$.a", b) from t01;
+---------------+-----------------------+
| b | json_set(b, "$.a", b) |
+---------------+-----------------------+
| {"a":1,"b":2} | {"a":1,"b":2} |
+---------------+-----------------------+
```
now
```
mysql> select b, json_set(b, "$.a", b) from t01;
+---------------+---------------------------+
| b | json_set(b, "$.a", b) |
+---------------+---------------------------+
| {"a":1,"b":2} | {"a":{"a":1,"b":2},"b":2} |
+---------------+---------------------------+
1 row in set (0.12 sec)
```
---
be/src/vec/functions/function_json.cpp | 4 +-
.../expressions/functions/scalar/JsonArray.java | 33 +++++++-----
.../expressions/functions/scalar/JsonInsert.java | 33 +++++++-----
.../expressions/functions/scalar/JsonReplace.java | 33 +++++++-----
.../expressions/functions/scalar/JsonSet.java | 33 +++++++-----
.../json_function/test_query_json_array.out | Bin 485 -> 1395 bytes
.../json_function/test_query_json_insert.out | Bin 423 -> 1785 bytes
.../json_function/test_query_json_replace.out | Bin 543 -> 1752 bytes
.../json_function/test_query_json_set.out | Bin 385 -> 1594 bytes
.../json_function/test_query_json_array.groovy | 52 +++++++++++++++++++
.../json_function/test_query_json_insert.groovy | 56 ++++++++++++++++++++
.../json_function/test_query_json_replace.groovy | 57 +++++++++++++++++++++
.../json_function/test_query_json_set.groovy | 56 ++++++++++++++++++++
13 files changed, 302 insertions(+), 55 deletions(-)
diff --git a/be/src/vec/functions/function_json.cpp
b/be/src/vec/functions/function_json.cpp
index bf8d8d7e43a..6901dfb2b69 100644
--- a/be/src/vec/functions/function_json.cpp
+++ b/be/src/vec/functions/function_json.cpp
@@ -646,7 +646,7 @@ struct FunctionJsonArrayImpl {
rapidjson::Document::AllocatorType& allocator,
const std::vector<const ColumnUInt8*>& nullmaps)
{
for (int i = 0; i < data_columns.size() - 1; i++) {
- constexpr_int_match<'0', '6', Reducer>::run(type_flags[i],
objects, allocator,
+ constexpr_int_match<'0', '7', Reducer>::run(type_flags[i],
objects, allocator,
data_columns[i],
nullmaps[i]);
}
}
@@ -1539,7 +1539,7 @@ public:
const std::vector<const ColumnUInt8*>& nullmaps,
std::vector<bool>& column_is_consts) {
for (auto col = 1; col + 1 < data_columns.size() - 1; col += 2) {
- constexpr_int_match<'0', '6', Reducer>::run(
+ constexpr_int_match<'0', '7', Reducer>::run(
type_flags[col + 1], objects, json_paths[col / 2],
data_columns[col + 1],
nullmaps[col + 1], column_is_consts[col + 1]);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonArray.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonArray.java
index 51d8d4f4e6c..581b21bc589 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonArray.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonArray.java
@@ -20,24 +20,20 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
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_array'. This class is generated by GenerateFunction.
*/
-public class JsonArray extends ScalarFunction
- implements ExplicitlyCastableSignature, AlwaysNotNullable {
-
- public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT).varArgs(VarcharType.SYSTEM_DEFAULT)
- );
+public class JsonArray extends ScalarFunction implements CustomSignature,
AlwaysNotNullable {
/**
* constructor with 0 or more arguments.
@@ -46,6 +42,20 @@ public class JsonArray extends ScalarFunction
super("json_array", ExpressionUtils.mergeArguments(varArgs));
}
+ @Override
+ public FunctionSignature customSignature() {
+ List<DataType> arguments = new ArrayList<>();
+ for (int i = 0; i < arity(); i++) {
+ if (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);
+ }
+
/**
* withChildren.
*/
@@ -54,11 +64,6 @@ public class JsonArray extends ScalarFunction
return new JsonArray(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.visitJsonArray(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonInsert.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonInsert.java
index 8f9c9900aec..397067509c6 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonInsert.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonInsert.java
@@ -19,26 +19,23 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
-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.functions.PropagateNullable;
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.base.Preconditions;
-import com.google.common.collect.ImmutableList;
+import java.util.ArrayList;
import java.util.List;
/**
* ScalarFunction 'json_insert'. This class is generated by GenerateFunction.
*/
-public class JsonInsert extends ScalarFunction
- implements ExplicitlyCastableSignature, PropagateNullable {
-
- public static final List<FunctionSignature> SIGNATURES =
- ImmutableList.of(FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT)
- .varArgs(VarcharType.SYSTEM_DEFAULT, VarcharType.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT));
+public class JsonInsert extends ScalarFunction implements CustomSignature,
PropagateNullable {
/**
* constructor with 3 or more arguments.
@@ -47,6 +44,21 @@ public class JsonInsert extends ScalarFunction
super("json_insert", ExpressionUtils.mergeArguments(arg0, arg1, arg2,
varArgs));
}
+ @Override
+ public FunctionSignature customSignature() {
+ List<DataType> arguments = new ArrayList<>();
+ arguments.add(VarcharType.SYSTEM_DEFAULT); // json_str
+ for (int i = 1; i < arity(); i++) {
+ if ((i & 1) == 0 && (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);
+ }
+
/**
* withChildren.
*/
@@ -57,11 +69,6 @@ public class JsonInsert extends ScalarFunction
children.subList(3, children.size()).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.visitJsonInsert(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonReplace.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonReplace.java
index c5ced84ac8e..69e424df1fd 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonReplace.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonReplace.java
@@ -19,26 +19,23 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
-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.functions.PropagateNullable;
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.base.Preconditions;
-import com.google.common.collect.ImmutableList;
+import java.util.ArrayList;
import java.util.List;
/**
* ScalarFunction 'json_replace'. This class is generated by GenerateFunction.
*/
-public class JsonReplace extends ScalarFunction
- implements ExplicitlyCastableSignature, PropagateNullable {
-
- public static final List<FunctionSignature> SIGNATURES =
- ImmutableList.of(FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT)
- .varArgs(VarcharType.SYSTEM_DEFAULT, VarcharType.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT));
+public class JsonReplace extends ScalarFunction implements CustomSignature,
PropagateNullable {
/**
* constructor with 3 or more arguments.
@@ -47,6 +44,21 @@ public class JsonReplace extends ScalarFunction
super("json_replace", ExpressionUtils.mergeArguments(arg0, arg1, arg2,
varArgs));
}
+ @Override
+ public FunctionSignature customSignature() {
+ List<DataType> arguments = new ArrayList<>();
+ arguments.add(VarcharType.SYSTEM_DEFAULT); // json_str
+ for (int i = 1; i < arity(); i++) {
+ if ((i & 1) == 0 && (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);
+ }
+
/**
* withChildren.
*/
@@ -57,11 +69,6 @@ public class JsonReplace extends ScalarFunction
children.subList(3, children.size()).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.visitJsonReplace(this, context);
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSet.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSet.java
index 1076e7ff4ec..f3b1345f823 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSet.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSet.java
@@ -19,26 +19,23 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
-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.functions.PropagateNullable;
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.base.Preconditions;
-import com.google.common.collect.ImmutableList;
+import java.util.ArrayList;
import java.util.List;
/**
* ScalarFunction 'json_set'. This class is generated by GenerateFunction.
*/
-public class JsonSet extends ScalarFunction
- implements ExplicitlyCastableSignature, PropagateNullable {
-
- public static final List<FunctionSignature> SIGNATURES =
- ImmutableList.of(FunctionSignature.ret(VarcharType.SYSTEM_DEFAULT)
- .varArgs(VarcharType.SYSTEM_DEFAULT, VarcharType.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT));
+public class JsonSet extends ScalarFunction implements CustomSignature,
PropagateNullable {
/**
* constructor with 3 or more arguments.
@@ -47,6 +44,21 @@ public class JsonSet extends ScalarFunction
super("json_set", ExpressionUtils.mergeArguments(arg0, arg1, arg2,
varArgs));
}
+ @Override
+ public FunctionSignature customSignature() {
+ List<DataType> arguments = new ArrayList<>();
+ arguments.add(VarcharType.SYSTEM_DEFAULT); // json_str
+ for (int i = 1; i < arity(); i++) {
+ if ((i & 1) == 0 && (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);
+ }
+
/**
* withChildren.
*/
@@ -57,11 +69,6 @@ public class JsonSet extends ScalarFunction
children.subList(3, children.size()).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.visitJsonSet(this, context);
diff --git
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_array.out
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_array.out
index 394d5580ee8..5e170954a96 100644
Binary files
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_array.out
and
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_array.out
differ
diff --git
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_insert.out
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_insert.out
index 9aa3df9655c..63570a66228 100644
Binary files
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_insert.out
and
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_insert.out
differ
diff --git
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_replace.out
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_replace.out
index 3ba95483878..d26983242e3 100644
Binary files
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_replace.out
and
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_replace.out
differ
diff --git
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_set.out
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_set.out
index b289ec01b1c..0742d28e1f3 100644
Binary files
a/regression-test/data/query_p0/sql_functions/json_function/test_query_json_set.out
and
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_set.out
differ
diff --git
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_array.groovy
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_array.groovy
index 32a790be7ff..e33df09f1c7 100644
---
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_array.groovy
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_array.groovy
@@ -41,4 +41,56 @@ suite("test_query_json_array", "query") {
sql "insert into ${tableName} values(4,null,null,'test','2022-01-01
11:11:11');"
sql "insert into ${tableName} values(5,1,true,'test','2022-01-01
11:11:11');"
qt_sql2 """select json_array('k0',k0,'k1',k1,'k2',k2,'k3',k3,'k4',k4,'k5',
null,'k6','k6') from ${tableName} order by k0"""
+ sql "DROP TABLE ${tableName};"
+
+ // test json_array with complex type
+ // array
+ qt_sql_array """ SELECT json_array(array('"aaa"','"bbb"')); """
+ qt_sql_array """ SELECT json_array(array('aaa','bbb')); """
+ qt_sql_array """ SELECT json_array(array(1,2)); """
+ qt_sql_array """ SELECT json_array(array(1.1,2.2)); """
+ qt_sql_array """ SELECT json_array(array(1.1,2)); """
+ qt_sql_array """ SELECT json_array(array(cast(1 as decimal), cast(1.2 as
decimal))); """
+ // map
+ qt_sql_map """ SELECT json_array(map('a', 'b', 'c', 'd')); """
+ qt_sql_map """ SELECT json_array(map('a', 1, 'c', 2)); """
+ qt_sql_map """ SELECT json_array(map('a', 1.1, 'c', 2.2)); """
+ qt_sql_map """ SELECT json_array(map('a', 1.1, 'c', 2)); """
+ qt_sql_map """ SELECT json_array(map('a', cast(1 as decimal), 'c',
cast(1.2 as decimal))); """
+ // struct
+ qt_sql_struct """ SELECT json_array(named_struct('name', 'a', 'age', 1));
"""
+ qt_sql_struct """ SELECT json_array(named_struct('name', 'a', 'age',
1.1)); """
+ qt_sql_struct """ SELECT json_array(named_struct('name', 'a', 'age',
cast(1 as decimal))); """
+ // json
+ qt_sql_json """ SELECT json_array(cast('{\"a\":\"b\"}' as JSON)); """
+ qt_sql_json """ SELECT json_array(cast('{\"a\":1}' as JSON)); """
+ qt_sql_json """ SELECT json_array(cast('{\"a\":1.1}' as JSON)); """
+
+ // test with table
+ tableName = "test_query_json_array_complex"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `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_sql3 "select json_array(k0,k1,k2,k3,k4) from ${tableName} order by k0;"
+ sql "DROP TABLE ${tableName};"
}
diff --git
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_insert.groovy
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_insert.groovy
index fd70d41ee9d..7afb9a5e6c5 100644
---
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_insert.groovy
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_insert.groovy
@@ -41,4 +41,60 @@ suite("test_query_json_insert", "query,arrow_flight_sql") {
sql "insert into ${tableName} values(3,null,9);"
sql "insert into ${tableName} values(4,null,null);"
qt_sql1 "select json_insert('{\"id\": 0, \"time\": \"1970-01-01
00:00:00\", \"a1\": [1, 2], \"a2\": [1, 2]}', '\$.id', id, '\$.time', time,
'\$.a1[1]', k, '\$.a2[3]', k) from ${tableName} order by id;"
+ sql "DROP TABLE ${tableName};"
+
+ // test json_insert with complex type
+ // array
+ qt_sql_array """ SELECT json_insert('{"data": {}}', '\$.data.arr',
array('"aaa"','"bbb"')); """
+ qt_sql_array """ SELECT json_insert('{"data": {}}', '\$.data.arr',
array('aaa','bbb')); """
+ qt_sql_array """ SELECT json_insert('{"data": {}}', '\$.data.arr',
array(1,2)); """
+ qt_sql_array """ SELECT json_insert('{"data": {}}', '\$.data.arr',
array(1.1,2.2)); """
+ qt_sql_array """ SELECT json_insert('{"data": {}}', '\$.data.arr',
array(1.1,2)); """
+ qt_sql_array """ SELECT json_insert('{"data": {}}', '\$.data.arr',
array(cast(1 as decimal), cast(1.2 as decimal))); """
+ // map
+ qt_sql_map """ SELECT json_insert('{"data": {}}', '\$.data.map', map('a',
'b', 'c', 'd')); """
+ qt_sql_map """ SELECT json_insert('{"data": {}}', '\$.data.map', map('a',
1, 'c', 2)); """
+ qt_sql_map """ SELECT json_insert('{"data": {}}', '\$.data.map', map('a',
1.1, 'c', 2.2)); """
+ qt_sql_map """ SELECT json_insert('{"data": {}}', '\$.data.map', map('a',
1.1, 'c', 2)); """
+ qt_sql_map """ SELECT json_insert('{"data": {}}', '\$.data.map', map('a',
cast(1 as decimal), 'c', cast(1.2 as decimal))); """
+ // struct
+ qt_sql_struct """ SELECT json_insert('{"data": {}}', '\$.data.struct',
named_struct('name', 'a', 'age', 1)); """
+ qt_sql_struct """ SELECT json_insert('{"data": {}}', '\$.data.struct',
named_struct('name', 'a', 'age', 1.1)); """
+ qt_sql_struct """ SELECT json_insert('{"data": {}}', '\$.data.struct',
named_struct('name', 'a', 'age', cast(1 as decimal))); """
+ // json
+ qt_sql_json """ SELECT json_insert('{"data": {}}', '\$.data.json',
cast('{\"a\":\"b\"}' as JSON)); """
+ qt_sql_json """ SELECT json_insert('{"data": {}}', '\$.data.json',
cast('{\"a\":1}' as JSON)); """
+ qt_sql_json """ SELECT json_insert('{"data": {}}', '\$.data.json',
cast('{\"a\":1.1}' as JSON)); """
+
+ // test with table
+ tableName = "test_query_json_insert_complex"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `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_insert('{"data": {}}',
+ '\$.data.array', k1,
+ '\$.data.map', k2,
+ '\$.data.struct', k3,
+ '\$.data.json', k4) from ${tableName} order by
k0;"""
+ sql "DROP TABLE ${tableName};"
}
diff --git
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_replace.groovy
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_replace.groovy
index 97ec3d1a202..e0ffe58af29 100644
---
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_replace.groovy
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_replace.groovy
@@ -63,4 +63,61 @@ suite("test_query_json_replace", "query") {
sql "insert into test_query_json_replace_nullable values(2,'2022-01-01
11:45:14',null);"
sql "insert into test_query_json_replace_nullable values(3,null,9);"
qt_sql2 "select json_replace('{\"id\": 0, \"time\": \"1970-01-01
00:00:00\", \"a1\": [1, 2], \"a2\": [1, 2]}', '\$.id', id, '\$.time', time,
'\$.a1[1]', k, '\$.a2[3]', k) from test_query_json_replace_nullable order by
id;"
+
+ sql "DROP TABLE test_query_json_replace_nullable;"
+
+ // test json_replace with complex type
+ // array
+ qt_sql_array """ SELECT json_replace('{"arr": [1,2]}', '\$.arr',
array('"aaa"','"bbb"')); """
+ qt_sql_array """ SELECT json_replace('{"arr": [1,2]}', '\$.arr',
array('aaa','bbb')); """
+ qt_sql_array """ SELECT json_replace('{"arr": [1,2]}', '\$.arr',
array(1,2)); """
+ qt_sql_array """ SELECT json_replace('{"arr": [1,2]}', '\$.arr',
array(1.1,2.2)); """
+ qt_sql_array """ SELECT json_replace('{"arr": [1,2]}', '\$.arr',
array(1.1,2)); """
+ qt_sql_array """ SELECT json_replace('{"arr": [1,2]}', '\$.arr',
array(cast(1 as decimal), cast(1.2 as decimal))); """
+ // map
+ qt_sql_map """ SELECT json_replace('{"map": {"x": "y"}}', '\$.map',
map('a', 'b', 'c', 'd')); """
+ qt_sql_map """ SELECT json_replace('{"map": {"x": "y"}}', '\$.map',
map('a', 1, 'c', 2)); """
+ qt_sql_map """ SELECT json_replace('{"map": {"x": "y"}}', '\$.map',
map('a', 1.1, 'c', 2.2)); """
+ qt_sql_map """ SELECT json_replace('{"map": {"x": "y"}}', '\$.map',
map('a', 1.1, 'c', 2)); """
+ qt_sql_map """ SELECT json_replace('{"map": {"x": "y"}}', '\$.map',
map('a', cast(1 as decimal), 'c', cast(1.2 as decimal))); """
+ // struct
+ qt_sql_struct """ SELECT json_replace('{"struct": {"name": "x", "age":
0}}', '\$.struct', named_struct('name', 'a', 'age', 1)); """
+ qt_sql_struct """ SELECT json_replace('{"struct": {"name": "x", "age":
0}}', '\$.struct', named_struct('name', 'a', 'age', 1.1)); """
+ qt_sql_struct """ SELECT json_replace('{"struct": {"name": "x", "age":
0}}', '\$.struct', named_struct('name', 'a', 'age', cast(1 as decimal))); """
+ // json
+ qt_sql_json """ SELECT json_replace('{"json": {"x": "y"}}', '\$.json',
cast('{\"a\":\"b\"}' as JSON)); """
+ qt_sql_json """ SELECT json_replace('{"json": {"x": "y"}}', '\$.json',
cast('{\"a\":1}' as JSON)); """
+ qt_sql_json """ SELECT json_replace('{"json": {"x": "y"}}', '\$.json',
cast('{\"a\":1.1}' as JSON)); """
+
+ // test with table
+ tableName = "test_query_json_replace_complex"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `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_sql3 """select json_replace('{"data": {"array": [1], "map": {"x":"y"},
"struct": {"name":"x","age":0}, "json": {"x":"y"}}}',
+ '\$.data.array', k1,
+ '\$.data.map', k2,
+ '\$.data.struct', k3,
+ '\$.data.json', k4) from ${tableName} order by
k0;"""
+ sql "DROP TABLE ${tableName};"
}
diff --git
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_set.groovy
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_set.groovy
index 4ba696907fb..6f04e4be88a 100644
---
a/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_set.groovy
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_set.groovy
@@ -41,4 +41,60 @@ suite("test_query_json_set", "query") {
sql "insert into ${tableName} values(3,null,9);"
sql "insert into ${tableName} values(4,null,null);"
qt_sql1 "select json_set('{\"id\": 0, \"time\": \"1970-01-01 00:00:00\",
\"a1\": [1, 2], \"a2\": [1, 2]}', '\$.id', id, '\$.time', time, '\$.a1[1]', k,
'\$.a2[3]', k) from ${tableName} order by id;"
+ sql "DROP TABLE ${tableName};"
+
+ // test json_set with complex type
+ // array
+ qt_sql_array """ SELECT json_set('{"arr": null}', '\$.arr',
array('"aaa"','"bbb"')); """
+ qt_sql_array """ SELECT json_set('{"arr": null}', '\$.arr',
array('aaa','bbb')); """
+ qt_sql_array """ SELECT json_set('{"arr": null}', '\$.arr', array(1,2));
"""
+ qt_sql_array """ SELECT json_set('{"arr": null}', '\$.arr',
array(1.1,2.2)); """
+ qt_sql_array """ SELECT json_set('{"arr": null}', '\$.arr', array(1.1,2));
"""
+ qt_sql_array """ SELECT json_set('{"arr": null}', '\$.arr', array(cast(1
as decimal), cast(1.2 as decimal))); """
+ // map
+ qt_sql_map """ SELECT json_set('{"map": null}', '\$.map', map('a', 'b',
'c', 'd')); """
+ qt_sql_map """ SELECT json_set('{"map": null}', '\$.map', map('a', 1, 'c',
2)); """
+ qt_sql_map """ SELECT json_set('{"map": null}', '\$.map', map('a', 1.1,
'c', 2.2)); """
+ qt_sql_map """ SELECT json_set('{"map": null}', '\$.map', map('a', 1.1,
'c', 2)); """
+ qt_sql_map """ SELECT json_set('{"map": null}', '\$.map', map('a', cast(1
as decimal), 'c', cast(1.2 as decimal))); """
+ // struct
+ qt_sql_struct """ SELECT json_set('{"struct": null}', '\$.struct',
named_struct('name', 'a', 'age', 1)); """
+ qt_sql_struct """ SELECT json_set('{"struct": null}', '\$.struct',
named_struct('name', 'a', 'age', 1.1)); """
+ qt_sql_struct """ SELECT json_set('{"struct": null}', '\$.struct',
named_struct('name', 'a', 'age', cast(1 as decimal))); """
+ // json
+ qt_sql_json """ SELECT json_set('{"json": null}', '\$.json',
cast('{\"a\":\"b\"}' as JSON)); """
+ qt_sql_json """ SELECT json_set('{"json": null}', '\$.json',
cast('{\"a\":1}' as JSON)); """
+ qt_sql_json """ SELECT json_set('{"json": null}', '\$.json',
cast('{\"a\":1.1}' as JSON)); """
+
+ // test with table
+ tableName = "test_query_json_set_complex"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `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_set('{"data": {"array": null, "map": null,
"struct": null, "json": null}}',
+ '\$.data.array', k1,
+ '\$.data.map', k2,
+ '\$.data.struct', k3,
+ '\$.data.json', k4) from ${tableName} order by
k0;"""
+ sql "DROP TABLE ${tableName};"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
