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 1b95ce1d93 [feature](json-function) add json_insert, json_replace,
json_set functions (#24384)
1b95ce1d93 is described below
commit 1b95ce1d93996fbd6953e401fde05ad954d4d726
Author: xfz <73645462+xuefen...@users.noreply.github.com>
AuthorDate: Mon Sep 25 12:52:29 2023 +0800
[feature](json-function) add json_insert, json_replace, json_set functions
(#24384)
[feature](json-function) add three json funcitons
---
be/src/vec/functions/function_json.cpp | 325 +++++++++++++++++++++
.../sql-functions/json-functions/json-insert.md | 75 +++++
.../sql-functions/json-functions/json-replace.md | 71 +++++
.../sql-functions/json-functions/json-set.md | 75 +++++
docs/sidebars.json | 6 +-
.../sql-functions/json-functions/json-insert.md | 74 +++++
.../sql-functions/json-functions/json-replace.md | 70 +++++
.../sql-functions/json-functions/json-set.md | 73 +++++
.../apache/doris/analysis/FunctionCallExpr.java | 48 ++-
.../doris/catalog/BuiltinScalarFunctions.java | 6 +
.../expressions/functions/scalar/JsonInsert.java | 70 +++++
.../expressions/functions/scalar/JsonReplace.java | 70 +++++
.../expressions/functions/scalar/JsonSet.java | 70 +++++
.../expressions/visitor/ScalarFunctionVisitor.java | 15 +
.../doris/nereids/util/TypeCoercionUtils.java | 40 +++
gensrc/script/doris_builtins_functions.py | 7 +-
.../json_function/test_query_json_insert.out | 13 +
.../json_function/test_query_json_replace.out | 13 +
.../json_function/test_query_json_set.out | 12 +
.../json_function/test_query_json_insert.out | 13 +
.../json_function/test_query_json_replace.out | 13 +
.../json_function/test_query_json_set.out | 13 +
.../json_function/test_query_json_insert.groovy | 48 +++
.../json_function/test_query_json_replace.groovy | 48 +++
.../json_function/test_query_json_set.groovy | 48 +++
.../json_function/test_query_json_insert.groovy | 45 +++
.../json_function/test_query_json_replace.groovy | 45 +++
.../json_function/test_query_json_set.groovy | 45 +++
28 files changed, 1443 insertions(+), 8 deletions(-)
diff --git a/be/src/vec/functions/function_json.cpp
b/be/src/vec/functions/function_json.cpp
index 7ea820b1cf..124015818a 100644
--- a/be/src/vec/functions/function_json.cpp
+++ b/be/src/vec/functions/function_json.cpp
@@ -1136,6 +1136,327 @@ public:
}
};
+enum class JsonModifyType {
+ JSON_INSERT = 0,
+ JSON_REPLACE,
+ JSON_SET,
+};
+
+struct FunctionJsonInsert {
+ static constexpr auto name = "json_insert";
+ static constexpr auto modify_type = JsonModifyType::JSON_INSERT;
+};
+
+struct FunctionJsonReplace {
+ static constexpr auto name = "json_replace";
+ static constexpr auto modify_type = JsonModifyType::JSON_REPLACE;
+};
+struct FunctionJsonSet {
+ static constexpr auto name = "json_set";
+ static constexpr auto modify_type = JsonModifyType::JSON_SET;
+};
+
+template <typename Kind>
+class FunctionJsonModifyImpl : public IFunction {
+private:
+ // T = std::vector<std::string>
+ // TODO: update RE2 to support std::vector<std::string_view>
+ // if path is not a valid path expression or contains
+ // a * wildcard, return runtime error.
+ template <typename T>
+ Status get_parsed_paths_with_status(const T& path_exprs,
std::vector<JsonPath>* parsed_paths) {
+ if (UNLIKELY(path_exprs.empty())) {
+ return Status::RuntimeError("json path empty function {}",
get_name());
+ }
+
+ if (path_exprs[0] != "$") {
+ // keep same behaviour with get_parsed_paths(),
+ // '$[0]' is invalid path, '$.[0]' is valid
+ return Status::RuntimeError(
+ "Invalid JSON path expression. The error is around
character position 1");
+ }
+ parsed_paths->emplace_back("$", -1, true);
+
+ for (int i = 1; i < path_exprs.size(); i++) {
+ std::string col;
+ std::string index;
+ if (UNLIKELY(!RE2::FullMatch(path_exprs[i], JSON_PATTERN, &col,
&index))) {
+ return Status::RuntimeError(
+ "Invalid JSON path expression. The error is around
character position {}",
+ i + 1);
+ } else {
+ int idx = -1;
+ if (!index.empty()) {
+ if (index == "*") {
+ return Status::RuntimeError(
+ "In this situation, path expressions may not
contain the * token");
+ } else {
+ idx = atoi(index.c_str());
+ }
+ }
+ parsed_paths->emplace_back(col, idx, true);
+ }
+ }
+ return Status::OK();
+ }
+
+ Status
get_parsed_path_columns(std::vector<std::vector<std::vector<JsonPath>>>&
json_paths,
+ const std::vector<const ColumnString*>&
data_columns,
+ size_t input_rows_count) {
+ for (auto col = 1; col + 1 < data_columns.size() - 1; col += 2) {
+ json_paths.emplace_back(std::vector<std::vector<JsonPath>>());
+ for (auto row = 0; row < input_rows_count; row++) {
+ const auto path = data_columns[col]->get_data_at(row);
+ std::string_view path_string(path.data, path.size);
+ std::vector<JsonPath> parsed_paths;
+
+#ifdef USE_LIBCPP
+ std::string s(path_string);
+ auto tok = get_json_token(s);
+#else
+ auto tok = get_json_token(path_string);
+#endif
+ std::vector<std::string> paths(tok.begin(), tok.end());
+ RETURN_IF_ERROR(get_parsed_paths_with_status(paths,
&parsed_paths));
+ json_paths[col / 2].emplace_back(parsed_paths);
+ }
+ }
+ return Status::OK();
+ }
+
+public:
+ static constexpr auto name = Kind::name;
+
+ static FunctionPtr create() { return
std::make_shared<FunctionJsonModifyImpl<Kind>>(); }
+
+ String get_name() const override { return name; }
+
+ size_t get_number_of_arguments() const override { return 0; }
+
+ bool is_variadic() const override { return true; }
+
+ bool use_default_implementation_for_nulls() const override { return false;
}
+
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ bool is_nullable = false;
+ // arguments: (json_str, path, val[, path, val...], type_flag)
+ for (auto col = 2; col < arguments.size() - 1; col += 2) {
+ if (arguments[col]->is_nullable()) {
+ is_nullable = true;
+ break;
+ }
+ }
+ return is_nullable ? make_nullable(std::make_shared<DataTypeString>())
+ : std::make_shared<DataTypeString>();
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) override {
+ auto result_column = ColumnString::create();
+ bool is_nullable = false;
+ auto ret_null_map = ColumnUInt8::create(0, 0);
+
+ std::vector<ColumnPtr> column_ptrs; // prevent converted column
destruct
+ std::vector<const ColumnString*> data_columns;
+ std::vector<const ColumnUInt8*> nullmaps;
+ for (int i = 0; i < arguments.size(); i++) {
+ auto column = block.get_by_position(arguments[i]).column;
+ column_ptrs.push_back(column->convert_to_full_column_if_const());
+ const ColumnNullable* col_nullable =
+
check_and_get_column<ColumnNullable>(column_ptrs.back().get());
+ if (col_nullable) {
+ if (!is_nullable) {
+ is_nullable = true;
+ ret_null_map = ColumnUInt8::create(input_rows_count, 0);
+ }
+ const ColumnUInt8* col_nullmap =
check_and_get_column<ColumnUInt8>(
+ col_nullable->get_null_map_column_ptr().get());
+ nullmaps.push_back(col_nullmap);
+ const ColumnString* col = check_and_get_column<ColumnString>(
+ col_nullable->get_nested_column_ptr().get());
+ data_columns.push_back(col);
+ } else {
+ nullmaps.push_back(nullptr);
+ data_columns.push_back(assert_cast<const
ColumnString*>(column_ptrs.back().get()));
+ }
+ }
+
+ RETURN_IF_ERROR(execute_process(
+ data_columns,
*assert_cast<ColumnString*>(result_column.get()), input_rows_count,
+ nullmaps, is_nullable,
*assert_cast<ColumnUInt8*>(ret_null_map.get())));
+
+ if (is_nullable) {
+ block.replace_by_position(result,
ColumnNullable::create(std::move(result_column),
+
std::move(ret_null_map)));
+ } else {
+ block.get_by_position(result).column = std::move(result_column);
+ }
+ return Status::OK();
+ }
+
+ Status execute_process(const std::vector<const ColumnString*>&
data_columns,
+ ColumnString& result_column, size_t
input_rows_count,
+ const std::vector<const ColumnUInt8*> nullmaps,
bool is_nullable,
+ ColumnUInt8& ret_null_map) {
+ std::string type_flags =
data_columns.back()->get_data_at(0).to_string();
+
+ std::vector<rapidjson::Document> objects;
+ for (auto row = 0; row < input_rows_count; row++) {
+ objects.emplace_back(rapidjson::kNullType);
+ const auto json_doc = data_columns[0]->get_data_at(row);
+ std::string_view json_str(json_doc.data, json_doc.size);
+ objects[row].Parse(json_str.data(), json_str.size());
+ if (UNLIKELY(objects[row].HasParseError())) {
+ return Status::RuntimeError("invalid json str {}: function
{}", json_str,
+ get_name());
+ }
+ }
+
+ std::vector<std::vector<std::vector<JsonPath>>> json_paths;
+ RETURN_IF_ERROR(get_parsed_path_columns(json_paths, data_columns,
input_rows_count));
+
+ execute_parse(type_flags, data_columns, objects, json_paths, nullmaps);
+
+ rapidjson::StringBuffer buf;
+ rapidjson::Writer<rapidjson::StringBuffer> writer(buf);
+
+ for (int i = 0; i < input_rows_count; i++) {
+ buf.Clear();
+ writer.Reset(buf);
+ objects[i].Accept(writer);
+ if (is_nullable && objects[i].IsNull()) {
+ ret_null_map.get_data()[i] = 1;
+ }
+ result_column.insert_data(buf.GetString(), buf.GetSize());
+ }
+ return Status::OK();
+ }
+
+ template <int flag>
+ using Reducer = ExecuteReducer<flag, FunctionJsonModifyImpl<Kind>>;
+
+ static void execute_parse(std::string type_flags,
+ const std::vector<const ColumnString*>&
data_columns,
+ std::vector<rapidjson::Document>& objects,
+ std::vector<std::vector<std::vector<JsonPath>>>&
json_paths,
+ const std::vector<const ColumnUInt8*>& nullmaps)
{
+ for (auto col = 1; col + 1 < data_columns.size() - 1; col += 2) {
+ constexpr_int_match<'0', '6', Reducer>::run(type_flags[col + 1],
objects,
+ json_paths[col / 2],
data_columns[col + 1],
+ nullmaps[col + 1]);
+ }
+ }
+
+ static void modify_value(const std::vector<JsonPath>& parsed_paths,
rapidjson::Value* document,
+ rapidjson::Document::AllocatorType&
mem_allocator, bool is_insert,
+ bool is_replace, rapidjson::Value* value) {
+ rapidjson::Value* root = document;
+ rapidjson::Value key;
+
+ auto i = 1;
+ for (; i < parsed_paths.size(); i++) {
+ if (root->IsNull()) {
+ return;
+ }
+ const std::string& col = parsed_paths[i].key;
+ int index = parsed_paths[i].idx;
+ if (LIKELY(!col.empty())) {
+ if (root->IsObject()) {
+ if (!root->HasMember(col.c_str())) {
+ break;
+ } else {
+ root = &((*root)[col.c_str()]);
+ }
+ } else {
+ // not object
+ return;
+ }
+ }
+ if (UNLIKELY(index != -1)) {
+ if (root->IsArray()) {
+ if (index >= root->Size()) {
+ // array append new value
+ if (is_insert && i + 1 == parsed_paths.size()) {
+ root->PushBack(*value, mem_allocator);
+ }
+ return;
+ } else {
+ root = &((*root)[index]);
+ }
+ } else {
+ if (i + 1 == parsed_paths.size()) {
+ // replace, example:
+ // json_replace({"a": 1}, '$.[0]', null);
+ // output: null
+ if (is_replace && index == 0) {
+ *root = *value;
+ return;
+ }
+ // convert to array, example:
+ // json_insert({"a": 1}, '$.[1]', 3);
+ // output: [{"a": 1}, 3]
+ if (is_insert && index > 0) {
+ key.SetArray();
+ root->Swap(key);
+ root->PushBack(key, mem_allocator);
+ root->PushBack(*value, mem_allocator);
+ }
+ }
+ return;
+ }
+ }
+ }
+
+ if (is_insert && i + 1 == parsed_paths.size()) {
+ if (LIKELY(root->IsObject())) {
+ // object insert new value
+ const std::string& col = parsed_paths[i].key;
+ int index = parsed_paths[i].idx;
+ if (LIKELY(!col.empty() && index == -1)) {
+ key.SetString(col.c_str(), mem_allocator);
+ root->AddMember(key, *value, mem_allocator);
+ }
+ }
+ } else if (is_replace && i == parsed_paths.size()) {
+ *root = *value;
+ }
+ }
+
+ template <typename TypeImpl>
+ static void execute_type(std::vector<rapidjson::Document>& objects,
+ std::vector<std::vector<JsonPath>>& paths_column,
+ const ColumnString* value_column, const
ColumnUInt8* nullmap) {
+ StringParser::ParseResult result;
+ rapidjson::Value value;
+ for (auto row = 0; row < objects.size(); row++) {
+ std::vector<JsonPath>* parsed_paths = &paths_column[row];
+
+ if (nullmap != nullptr && nullmap->get_data()[row]) {
+ JsonParser<'0'>::update_value(result, value,
value_column->get_data_at(row),
+ objects[row].GetAllocator());
+ } else {
+ TypeImpl::update_value(result, value,
value_column->get_data_at(row),
+ objects[row].GetAllocator());
+ }
+
+ switch (Kind::modify_type) {
+ case JsonModifyType::JSON_INSERT:
+ modify_value(*parsed_paths, &objects[row],
objects[row].GetAllocator(), true, false,
+ &value);
+ break;
+ case JsonModifyType::JSON_REPLACE:
+ modify_value(*parsed_paths, &objects[row],
objects[row].GetAllocator(), false, true,
+ &value);
+ break;
+ case JsonModifyType::JSON_SET:
+ modify_value(*parsed_paths, &objects[row],
objects[row].GetAllocator(), true, true,
+ &value);
+ break;
+ }
+ }
+ }
+};
+
void register_function_json(SimpleFunctionFactory& factory) {
factory.register_function<FunctionGetJsonInt>();
factory.register_function<FunctionGetJsonBigInt>();
@@ -1150,6 +1471,10 @@ void register_function_json(SimpleFunctionFactory&
factory) {
factory.register_function<FunctionJsonValid>();
factory.register_function<FunctionJsonContains>();
+
+ factory.register_function<FunctionJsonModifyImpl<FunctionJsonInsert>>();
+ factory.register_function<FunctionJsonModifyImpl<FunctionJsonReplace>>();
+ factory.register_function<FunctionJsonModifyImpl<FunctionJsonSet>>();
}
} // namespace doris::vectorized
diff --git
a/docs/en/docs/sql-manual/sql-functions/json-functions/json-insert.md
b/docs/en/docs/sql-manual/sql-functions/json-functions/json-insert.md
new file mode 100644
index 0000000000..64ffef30c9
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/json-functions/json-insert.md
@@ -0,0 +1,75 @@
+---
+{
+ "title": "JSON_INSERT",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## json_insert
+
+<version since="dev"></version>
+
+### Description
+#### Syntax
+
+`VARCHAR json_insert(VARCHAR json_str, VARCHAR path, VARCHAR val[, VARCHAR
path, VARCHAR val] ...)`
+
+
+`json_set` function inserts data in a JSON and returns the result.Returns NULL
if `json_str` or `path` is NULL. Otherwise, an error occurs if the `json_str`
argument is not a valid JSON or any path argument is not a valid path
expression or contains a * wildcard.
+
+The path-value pairs are evaluated left to right.
+
+A path-value pair for a nonexisting path in the json adds the value to the
json if the path identifies one of these types of values:
+
+* A member not present in an existing object. The member is added to the
object and associated with the new value.
+
+* A position past the end of an existing array. The array is extended with the
new value. If the existing value is not an array, it is autowrapped as an
array, then extended with the new value.
+
+Otherwise, a path-value pair for a nonexisting path in the json is ignored and
has no effect.
+
+### example
+
+```
+MySQL> select json_insert(null, null, null);
++---------------------------------+
+| json_insert(NULL, NULL, 'NULL') |
++---------------------------------+
+| NULL |
++---------------------------------+
+
+MySQL> select json_insert('{"k": 1}', "$.k", 2);
++---------------------------------------+
+| json_insert('{\"k\": 1}', '$.k', '2') |
++---------------------------------------+
+| {"k":1} |
++---------------------------------------+
+
+MySQL> select json_insert('{"k": 1}', "$.j", 2);
++---------------------------------------+
+| json_insert('{\"k\": 1}', '$.j', '2') |
++---------------------------------------+
+| {"k":1,"j":2} |
++---------------------------------------+
+```
+
+### keywords
+JSON, json_insert
diff --git
a/docs/en/docs/sql-manual/sql-functions/json-functions/json-replace.md
b/docs/en/docs/sql-manual/sql-functions/json-functions/json-replace.md
new file mode 100644
index 0000000000..7fad215be5
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/json-functions/json-replace.md
@@ -0,0 +1,71 @@
+---
+{
+ "title": "JSON_REPLACE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## json_replace
+
+<version since="dev"></version>
+
+### Description
+#### Syntax
+
+`VARCHAR json_replace(VARCHAR json_str, VARCHAR path, VARCHAR val[, VARCHAR
path, VARCHAR val] ...)`
+
+
+`json_replace` function updates data in a JSON and returns the result.Returns
NULL if `json_str` or `path` is NULL. Otherwise, an error occurs if the
`json_str` argument is not a valid JSON or any path argument is not a valid
path expression or contains a * wildcard.
+
+The path-value pairs are evaluated left to right.
+
+A path-value pair for an existing path in the json overwrites the existing
json value with the new value.
+
+Otherwise, a path-value pair for a nonexisting path in the json is ignored and
has no effect.
+
+### example
+
+```
+MySQL> select json_replace(null, null, null);
++----------------------------------+
+| json_replace(NULL, NULL, 'NULL') |
++----------------------------------+
+| NULL |
++----------------------------------+
+
+MySQL> select json_replace('{"k": 1}', "$.k", 2);
++----------------------------------------+
+| json_replace('{\"k\": 1}', '$.k', '2') |
++----------------------------------------+
+| {"k":2} |
++----------------------------------------+
+
+MySQL> select json_replace('{"k": 1}', "$.j", 2);
++----------------------------------------+
+| json_replace('{\"k\": 1}', '$.j', '2') |
++----------------------------------------+
+| {"k":1} |
++----------------------------------------+
+```
+
+### keywords
+JSON, json_replace
diff --git a/docs/en/docs/sql-manual/sql-functions/json-functions/json-set.md
b/docs/en/docs/sql-manual/sql-functions/json-functions/json-set.md
new file mode 100644
index 0000000000..42b47c58a8
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/json-functions/json-set.md
@@ -0,0 +1,75 @@
+---
+{
+ "title": "JSON_SET",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## json_set
+
+<version since="dev"></version>
+
+### Description
+#### Syntax
+
+`VARCHAR json_set(VARCHAR json_str, VARCHAR path, VARCHAR val[, VARCHAR path,
VARCHAR val] ...)`
+
+
+`json_set` function inserts or updates data in a JSON and returns the
result.Returns NULL if `json_str` or `path` is NULL. Otherwise, an error occurs
if the `json_str` argument is not a valid JSON or any path argument is not a
valid path expression or contains a * wildcard.
+
+The path-value pairs are evaluated left to right.
+
+A path-value pair for an existing path in the json overwrites the existing
json value with the new value. A path-value pair for a nonexisting path in the
json adds the value to the json if the path identifies one of these types of
values:
+
+* A member not present in an existing object. The member is added to the
object and associated with the new value.
+
+* A position past the end of an existing array. The array is extended with the
new value. If the existing value is not an array, it is autowrapped as an
array, then extended with the new value.
+
+Otherwise, a path-value pair for a nonexisting path in the json is ignored and
has no effect.
+
+### example
+
+```
+MySQL> select json_set(null, null, null);
++------------------------------+
+| json_set(NULL, NULL, 'NULL') |
++------------------------------+
+| NULL |
++------------------------------+
+
+MySQL> select json_set('{"k": 1}', "$.k", 2);
++------------------------------------+
+| json_set('{\"k\": 1}', '$.k', '2') |
++------------------------------------+
+| {"k":2} |
++------------------------------------+
+
+MySQL> select json_set('{"k": 1}', "$.j", 2);
++------------------------------------+
+| json_set('{\"k\": 1}', '$.j', '2') |
++------------------------------------+
+| {"k":1,"j":2} |
++------------------------------------+
+```
+
+### keywords
+JSON, json_set
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 5bb5d7db51..409dad1310 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -642,8 +642,10 @@
"sql-manual/sql-functions/json-functions/get-json-double",
"sql-manual/sql-functions/json-functions/get-json-int",
"sql-manual/sql-functions/json-functions/get-json-bigint",
-
"sql-manual/sql-functions/json-functions/get-json-string"
-
+
"sql-manual/sql-functions/json-functions/get-json-string",
+
"sql-manual/sql-functions/json-functions/json-insert",
+
"sql-manual/sql-functions/json-functions/json-replace",
+
"sql-manual/sql-functions/json-functions/json-set"
]
},
{
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-insert.md
b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-insert.md
new file mode 100644
index 0000000000..52948c1021
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-insert.md
@@ -0,0 +1,74 @@
+---
+{
+ "title": "JSON_INSERT",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## json_insert
+
+<version since="dev"></version>
+
+### Description
+#### Syntax
+
+`VARCHAR json_insert(VARCHAR json_str, VARCHAR path, VARCHAR val[, VARCHAR
path, VARCHAR val] ...)`
+
+
+`json_set` 函数在 JSON 中插入数据并返回结果。如果 `json_str` 或 `path` 为 NULL，则返回 NULL。否则，如果
`json_str` 不是有效的 JSON 或任何 `path` 参数不是有效的路径表达式或包含了 * 通配符，则会返回错误。
+
+路径值对按从左到右的顺序进行评估。
+
+如果 JSON 中不存在该路径，则路径值对会添加该值到 JSON 中，如果路径标识某个类型的值，则：
+
+* 对于现有对象中不存在的成员，会将新成员添加到该对象中并与新值相关联。
+* 对于现有数组结束后的位置，该数组将扩展为包含新值。如果现有值不是数组，则自动转换为数组，然后再扩展为包含新值的数组。
+
+否则，对于 JSON 中不存在的某个路径的路径值对将被忽略且不会产生任何影响。
+
+### example
+
+```
+MySQL> select json_insert(null, null, null);
++---------------------------------+
+| json_insert(NULL, NULL, 'NULL') |
++---------------------------------+
+| NULL |
++---------------------------------+
+
+MySQL> select json_insert('{"k": 1}', "$.k", 2);
++---------------------------------------+
+| json_insert('{\"k\": 1}', '$.k', '2') |
++---------------------------------------+
+| {"k":1} |
++---------------------------------------+
+
+MySQL> select json_insert('{"k": 1}', "$.j", 2);
++---------------------------------------+
+| json_insert('{\"k\": 1}', '$.j', '2') |
++---------------------------------------+
+| {"k":1,"j":2} |
++---------------------------------------+
+```
+
+### keywords
+JSON, json_insert
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-replace.md
b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-replace.md
new file mode 100644
index 0000000000..3db308350c
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-replace.md
@@ -0,0 +1,70 @@
+---
+{
+ "title": "JSON_REPLACE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## json_replace
+
+<version since="dev"></version>
+
+### Description
+#### Syntax
+
+`VARCHAR json_replace(VARCHAR json_str, VARCHAR path, VARCHAR val[, VARCHAR
path, VARCHAR val] ...)`
+
+
+`json_set` 函数在 JSON 中更新数据并返回结果。如果 `json_str` 或 `path` 为 NULL，则返回 NULL。否则，如果
`json_str` 不是有效的 JSON 或任何 `path` 参数不是有效的路径表达式或包含了 * 通配符，则会返回错误。
+
+路径值对按从左到右的顺序进行评估。
+
+如果 JSON 中已存在某个路径，则路径值对会将现有 JSON 值覆盖为新值。
+否则，对于 JSON 中不存在的某个路径的路径值对将被忽略且不会产生任何影响。
+
+### example
+
+```
+MySQL> select json_replace(null, null, null);
++----------------------------------+
+| json_replace(NULL, NULL, 'NULL') |
++----------------------------------+
+| NULL |
++----------------------------------+
+
+MySQL> select json_replace('{"k": 1}', "$.k", 2);
++----------------------------------------+
+| json_replace('{\"k\": 1}', '$.k', '2') |
++----------------------------------------+
+| {"k":2} |
++----------------------------------------+
+
+MySQL> select json_replace('{"k": 1}', "$.j", 2);
++----------------------------------------+
+| json_replace('{\"k\": 1}', '$.j', '2') |
++----------------------------------------+
+| {"k":1} |
++----------------------------------------+
+```
+
+### keywords
+JSON, json_replace
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-set.md
b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-set.md
new file mode 100644
index 0000000000..a61cae15db
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-set.md
@@ -0,0 +1,73 @@
+---
+{
+ "title": "JSON_SET",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## json_set
+
+<version since="dev"></version>
+
+### Description
+#### Syntax
+
+`VARCHAR json_set(VARCHAR json_str, VARCHAR path, VARCHAR val[, VARCHAR path,
VARCHAR val] ...)`
+
+`json_set` 函数在 JSON 中插入或更新数据并返回结果。如果 `json_str` 或 `path` 为 NULL，则返回 NULL。否则，如果
`json_str` 不是有效的 JSON 或任何 `path` 参数不是有效的路径表达式或包含了 * 通配符，则会返回错误。
+
+路径值对按从左到右的顺序进行评估。
+
+如果 JSON 中已存在某个路径，则路径值对会将现有 JSON 值覆盖为新值。如果 JSON 中不存在该路径，则路径值对会添加该值到 JSON
中，如果路径标识某个类型的值，则：
+
+* 对于现有对象中不存在的成员，会将新成员添加到该对象中并与新值相关联。
+* 对于现有数组结束后的位置，该数组将扩展为包含新值。如果现有值不是数组，则自动转换为数组，然后再扩展为包含新值的数组。
+
+否则，对于 JSON 中不存在的某个路径的路径值对将被忽略且不会产生任何影响。
+
+### example
+
+```
+MySQL> select json_set(null, null, null);
++------------------------------+
+| json_set(NULL, NULL, 'NULL') |
++------------------------------+
+| NULL |
++------------------------------+
+
+MySQL> select json_set('{"k": 1}', "$.k", 2);
++------------------------------------+
+| json_set('{\"k\": 1}', '$.k', '2') |
++------------------------------------+
+| {"k":2} |
++------------------------------------+
+
+MySQL> select json_set('{"k": 1}', "$.j", 2);
++------------------------------------+
+| json_set('{\"k\": 1}', '$.j', '2') |
++------------------------------------+
+| {"k":1,"j":2} |
++------------------------------------+
+```
+
+### keywords
+JSON, json_set
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 a161685dde..316a2594db 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
@@ -424,6 +424,18 @@ public class FunctionCallExpr extends Expr {
this.isTableFnCall = other.isTableFnCall;
}
+ public String parseJsonValueModifyDataType() {
+ StringBuilder sb = new StringBuilder();
+ for (int i = 0; i < children.size(); ++i) {
+ Type type = getChild(i).getType();
+ if (i > 0 && (i & 1) == 0 && type.isNull()) {
+ children.set(i, new StringLiteral("NULL"));
+ }
+ sb.append(computeJsonDataType(type));
+ }
+ return sb.toString();
+ }
+
public String parseJsonDataType(boolean useKeyCheck) throws
AnalysisException {
StringBuilder sb = new StringBuilder();
for (int i = 0; i < children.size(); ++i) {
@@ -586,7 +598,10 @@ public class FunctionCallExpr extends Expr {
// used by nereids END
if (fnName.getFunction().equalsIgnoreCase("json_array")
- || fnName.getFunction().equalsIgnoreCase("json_object")) {
+ || fnName.getFunction().equalsIgnoreCase("json_object")
+ || fnName.getFunction().equalsIgnoreCase("json_insert")
+ || fnName.getFunction().equalsIgnoreCase("json_replace")
+ || fnName.getFunction().equalsIgnoreCase("json_set")) {
len = len - 1;
}
@@ -649,7 +664,10 @@ public class FunctionCallExpr extends Expr {
sb.append(paramsToSql());
if (fnName.getFunction().equalsIgnoreCase("json_quote")
|| fnName.getFunction().equalsIgnoreCase("json_array")
- || fnName.getFunction().equalsIgnoreCase("json_object")) {
+ || fnName.getFunction().equalsIgnoreCase("json_object")
+ || fnName.getFunction().equalsIgnoreCase("json_insert")
+ || fnName.getFunction().equalsIgnoreCase("json_replace")
+ || fnName.getFunction().equalsIgnoreCase("json_set")) {
return forJSON(sb.toString());
}
}
@@ -669,7 +687,10 @@ public class FunctionCallExpr extends Expr {
int len = children.size();
List<String> result = Lists.newArrayList();
if (fnName.getFunction().equalsIgnoreCase("json_array")
- || fnName.getFunction().equalsIgnoreCase("json_object")) {
+ || fnName.getFunction().equalsIgnoreCase("json_object")
+ || fnName.getFunction().equalsIgnoreCase("json_insert")
+ || fnName.getFunction().equalsIgnoreCase("json_replace")
+ || fnName.getFunction().equalsIgnoreCase("json_set")) {
len = len - 1;
}
if (fnName.getFunction().equalsIgnoreCase("aes_decrypt")
@@ -713,7 +734,10 @@ public class FunctionCallExpr extends Expr {
sb.append(paramsToDigest());
if (fnName.getFunction().equalsIgnoreCase("json_quote")
|| fnName.getFunction().equalsIgnoreCase("json_array")
- || fnName.getFunction().equalsIgnoreCase("json_object")) {
+ || fnName.getFunction().equalsIgnoreCase("json_object")
+ || fnName.getFunction().equalsIgnoreCase("json_insert")
+ || fnName.getFunction().equalsIgnoreCase("json_replace")
+ || fnName.getFunction().equalsIgnoreCase("json_set")) {
return forJSON(sb.toString());
}
return sb.toString();
@@ -832,6 +856,22 @@ public class FunctionCallExpr extends Expr {
}
return;
}
+
+ if (fnName.getFunction().equalsIgnoreCase("json_insert")
+ || fnName.getFunction().equalsIgnoreCase("json_replace")
+ || fnName.getFunction().equalsIgnoreCase("json_set")) {
+ if (((children.size() & 1) == 0 || children.size() < 3)
+ && (originChildSize == children.size())) {
+ throw new AnalysisException(fnName.getFunction() + " need odd
parameters, and >= 3 arguments: "
+ + this.toSql());
+ }
+ String res = parseJsonValueModifyDataType();
+ if (children.size() == originChildSize) {
+ children.add(new StringLiteral(res));
+ }
+ return;
+ }
+
if (fnName.getFunction().equalsIgnoreCase("group_concat")) {
if (children.size() - orderByElements.size() > 2 ||
children.isEmpty()) {
throw new AnalysisException(
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
index e7d583bd04..7f8b4b4be7 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java
@@ -182,9 +182,12 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Instr;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonArray;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonContains;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonExtract;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonInsert;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonLength;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonObject;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonQuote;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSet;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonUnQuote;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonbExistsPath;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonbExtract;
@@ -562,6 +565,9 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(JsonQuote.class, "json_quote"),
scalar(JsonUnQuote.class, "json_unquote"),
scalar(JsonExtract.class, "json_extract"),
+ scalar(JsonInsert.class, "json_insert"),
+ scalar(JsonReplace.class, "json_replace"),
+ scalar(JsonSet.class, "json_set"),
scalar(JsonbExistsPath.class, "json_exists_path"),
scalar(JsonbExistsPath.class, "jsonb_exists_path"),
scalar(JsonbExtract.class, "jsonb_extract"),
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
new file mode 100644
index 0000000000..5e7805e7fb
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonInsert.java
@@ -0,0 +1,70 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.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.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+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.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));
+
+ /**
+ * constructor with 3 or more arguments.
+ */
+ public JsonInsert(Expression arg0, Expression arg1, Expression arg2,
Expression... varArgs) {
+ super("json_insert", ExpressionUtils.mergeArguments(arg0, arg1, arg2,
varArgs));
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public JsonInsert withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() >= 3);
+ return new JsonInsert(children.get(0), children.get(1),
children.get(2),
+ 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
new file mode 100644
index 0000000000..f3e735136e
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonReplace.java
@@ -0,0 +1,70 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.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.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+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.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));
+
+ /**
+ * constructor with 3 or more arguments.
+ */
+ public JsonReplace(Expression arg0, Expression arg1, Expression arg2,
Expression... varArgs) {
+ super("json_replace", ExpressionUtils.mergeArguments(arg0, arg1, arg2,
varArgs));
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public JsonReplace withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() >= 3);
+ return new JsonReplace(children.get(0), children.get(1),
children.get(2),
+ 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
new file mode 100644
index 0000000000..394d27fb9e
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSet.java
@@ -0,0 +1,70 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.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.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+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.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));
+
+ /**
+ * constructor with 3 or more arguments.
+ */
+ public JsonSet(Expression arg0, Expression arg1, Expression arg2,
Expression... varArgs) {
+ super("json_set", ExpressionUtils.mergeArguments(arg0, arg1, arg2,
varArgs));
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public JsonSet withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() >= 3);
+ return new JsonSet(children.get(0), children.get(1), children.get(2),
+ 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/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index 894c576366..3bd16bc79d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -180,9 +180,12 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Instr;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonArray;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonContains;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonExtract;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonInsert;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonLength;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonObject;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonQuote;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSet;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonUnQuote;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonbExistsPath;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonbExtract;
@@ -1062,6 +1065,18 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(jsonExtract, context);
}
+ default R visitJsonInsert(JsonInsert jsonInsert, C context) {
+ return visitScalarFunction(jsonInsert, context);
+ }
+
+ default R visitJsonReplace(JsonReplace jsonReplace, C context) {
+ return visitScalarFunction(jsonReplace, context);
+ }
+
+ default R visitJsonSet(JsonSet jsonSet, C context) {
+ return visitScalarFunction(jsonSet, context);
+ }
+
default R visitJsonQuote(JsonQuote jsonQuote, C context) {
return visitScalarFunction(jsonQuote, context);
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
index 34dacb4034..3aee0030d3 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/util/TypeCoercionUtils.java
@@ -46,7 +46,10 @@ import
org.apache.doris.nereids.trees.expressions.functions.BoundFunction;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Array;
import org.apache.doris.nereids.trees.expressions.functions.scalar.CreateMap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonArray;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonInsert;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonObject;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSet;
import org.apache.doris.nereids.trees.expressions.literal.BigIntLiteral;
import org.apache.doris.nereids.trees.expressions.literal.BooleanLiteral;
import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
@@ -524,6 +527,11 @@ public class TypeCoercionUtils {
if (boundFunction instanceof JsonArray || boundFunction instanceof
JsonObject) {
boundFunction =
TypeCoercionUtils.fillJsonTypeArgument(boundFunction, boundFunction instanceof
JsonObject);
}
+ if (boundFunction instanceof JsonInsert
+ || boundFunction instanceof JsonReplace
+ || boundFunction instanceof JsonSet) {
+ boundFunction =
TypeCoercionUtils.fillJsonValueModifyTypeArgument(boundFunction);
+ }
if (boundFunction instanceof CreateMap) {
return processCreateMap((CreateMap) boundFunction);
}
@@ -1425,6 +1433,38 @@ public class TypeCoercionUtils {
}
}
+ /**
+ * add json type info as the last argument of the function.
+ * used for json_insert, json_replace, json_set.
+ *
+ * @param function function need to add json type info
+ * @return function already processed
+ */
+ public static BoundFunction fillJsonValueModifyTypeArgument(BoundFunction
function) {
+ List<Expression> arguments = function.getArguments();
+ List<Expression> newArguments = Lists.newArrayList();
+ StringBuilder jsonTypeStr = new StringBuilder();
+ for (int i = 0; i < arguments.size(); i++) {
+ Expression argument = arguments.get(i);
+ Type type = argument.getDataType().toCatalogDataType();
+ int jsonType = FunctionCallExpr.computeJsonDataType(type);
+ jsonTypeStr.append(jsonType);
+
+ if (i > 0 && (i & 1) == 0 && type.isNull()) {
+ newArguments.add(new StringLiteral("NULL"));
+ } else {
+ newArguments.add(argument);
+ }
+ }
+ if (arguments.isEmpty()) {
+ newArguments.add(new StringLiteral(""));
+ } else {
+ // add json type string to the last
+ newArguments.add(new StringLiteral(jsonTypeStr.toString()));
+ }
+ return (BoundFunction) function.withChildren(newArguments);
+ }
+
private static Expression
processDecimalV3BinaryArithmetic(BinaryArithmetic binaryArithmetic,
Expression left, Expression right) {
DecimalV3Type dt1 =
diff --git a/gensrc/script/doris_builtins_functions.py
b/gensrc/script/doris_builtins_functions.py
index e9294ae977..95a4a77494 100644
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -790,7 +790,7 @@ visible_functions = {
[['array_pushback'], 'ARRAY_DECIMAL128', ['ARRAY_DECIMAL128',
'DECIMAL128'], 'ALWAYS_NULLABLE'],
[['array_pushback'], 'ARRAY_VARCHAR', ['ARRAY_VARCHAR', 'VARCHAR'],
'ALWAYS_NULLABLE'],
[['array_pushback'], 'ARRAY_STRING', ['ARRAY_STRING', 'STRING'],
'ALWAYS_NULLABLE'],
-
+
[['array_with_constant'], 'ARRAY_BOOLEAN', ['BIGINT', 'BOOLEAN'],
'ALWAYS_NOT_NULLABLE'],
[['array_with_constant'], 'ARRAY_TINYINT', ['BIGINT', 'TINYINT'],
'ALWAYS_NOT_NULLABLE'],
[['array_with_constant'], 'ARRAY_SMALLINT', ['BIGINT','SMALLINT'],
'ALWAYS_NOT_NULLABLE'],
@@ -1808,7 +1808,10 @@ visible_functions = {
[['json_valid'], 'INT', ['VARCHAR'], 'ALWAYS_NULLABLE'],
[['json_contains'], 'BOOLEAN', ['VARCHAR', 'VARCHAR', 'VARCHAR'],
'ALWAYS_NULLABLE'],
[['json_unquote'], 'VARCHAR', ['VARCHAR'], 'ALWAYS_NULLABLE'],
- [['json_extract'], 'VARCHAR', ['VARCHAR', 'VARCHAR', '...'], '']
+ [['json_extract'], 'VARCHAR', ['VARCHAR', 'VARCHAR', '...'], ''],
+ [['json_insert'], 'VARCHAR', ['VARCHAR', 'VARCHAR', '...'], ''],
+ [['json_replace'], 'VARCHAR', ['VARCHAR', 'VARCHAR', '...'], ''],
+ [['json_set'], 'VARCHAR', ['VARCHAR', 'VARCHAR', '...'], '']
],
diff --git
a/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_insert.out
b/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_insert.out
new file mode 100644
index 0000000000..34642f5599
--- /dev/null
+++
b/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_insert.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+{"a":1,"b":[2,3]}
+
+-- !sql --
+[{"k":[1,2]},null]
+
+-- !sql1 --
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,9]}
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,9]}
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,null]}
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,null]}
+
diff --git
a/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_replace.out
b/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_replace.out
new file mode 100644
index 0000000000..408aa2ecb7
--- /dev/null
+++
b/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_replace.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+null
+
+-- !sql --
+{"k":[null,2]}
+
+-- !sql1 --
+{"id":1,"time":"2022-01-01 11:45:14","a1":[1,9],"a2":[1,2]}
+{"id":2,"time":"2022-01-01 11:45:14","a1":[1,null],"a2":[1,2]}
+{"id":3,"time":null,"a1":[1,9],"a2":[1,2]}
+{"id":4,"time":null,"a1":[1,null],"a2":[1,2]}
+
diff --git
a/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_set.out
b/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_set.out
new file mode 100644
index 0000000000..89c200c560
--- /dev/null
+++
b/regression-test/data/nereids_p0/sql_functions/json_function/test_query_json_set.out
@@ -0,0 +1,12 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+null
+
+-- !sql --
+[{"k":[null,2]},null]
+
+-- !sql1 --
+{"id":1,"time":"2022-01-01 11:45:14","a1":[1,9],"a2":[1,2,9]}
+{"id":2,"time":"2022-01-01 11:45:14","a1":[1,null],"a2":[1,2,null]}
+{"id":3,"time":null,"a1":[1,9],"a2":[1,2,9]}
+{"id":4,"time":null,"a1":[1,null],"a2":[1,2,null]}
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
new file mode 100644
index 0000000000..9aa3df9655
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_insert.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+{"a":1,"b":[2,3]}
+
+-- !sql --
+[{"k":[1,2]},null]
+
+-- !sql1 --
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,9]}
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,null]}
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,9]}
+{"id":0,"time":"1970-01-01 00:00:00","a1":[1,2],"a2":[1,2,null]}
+
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
new file mode 100644
index 0000000000..408aa2ecb7
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_replace.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+null
+
+-- !sql --
+{"k":[null,2]}
+
+-- !sql1 --
+{"id":1,"time":"2022-01-01 11:45:14","a1":[1,9],"a2":[1,2]}
+{"id":2,"time":"2022-01-01 11:45:14","a1":[1,null],"a2":[1,2]}
+{"id":3,"time":null,"a1":[1,9],"a2":[1,2]}
+{"id":4,"time":null,"a1":[1,null],"a2":[1,2]}
+
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
new file mode 100644
index 0000000000..b289ec01b1
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/json_function/test_query_json_set.out
@@ -0,0 +1,13 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+null
+
+-- !sql --
+[{"k":[null,2]},null]
+
+-- !sql1 --
+{"id":1,"time":"2022-01-01 11:45:14","a1":[1,9],"a2":[1,2,9]}
+{"id":2,"time":"2022-01-01 11:45:14","a1":[1,null],"a2":[1,2,null]}
+{"id":3,"time":null,"a1":[1,9],"a2":[1,2,9]}
+{"id":4,"time":null,"a1":[1,null],"a2":[1,2,null]}
+
diff --git
a/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_insert.groovy
b/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_insert.groovy
new file mode 100644
index 0000000000..2bce28197b
--- /dev/null
+++
b/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_insert.groovy
@@ -0,0 +1,48 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_query_json_insert", "query") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ qt_sql "select json_insert('{\"a\": 1, \"b\": [2, 3]}', '\$', null);"
+ qt_sql "select json_insert('{\"k\": [1, 2]}', '\$.k[0]', null, '\$.[1]',
null);"
+ def tableName = "test_query_json_insert"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) not null,
+ `time` datetime,
+ `k` int(11)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`time`,`k`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql "insert into ${tableName} values(1,'2022-01-01 11:45:14',9);"
+ sql "insert into ${tableName} values(2,'2022-01-01 11:45:14',null);"
+ sql "insert into ${tableName} values(3,null,9);"
+ sql "insert into ${tableName} values(4,null,null);"
+ order_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};"
+ sql "DROP TABLE ${tableName};"
+}
diff --git
a/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_replace.groovy
b/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_replace.groovy
new file mode 100644
index 0000000000..6da73265ef
--- /dev/null
+++
b/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_replace.groovy
@@ -0,0 +1,48 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_query_json_replace", "query") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ qt_sql "select json_replace('{\"a\": 1, \"b\": [2, 3]}', '\$', null);"
+ qt_sql "select json_replace('{\"k\": [1, 2]}', '\$.k[0]', null, '\$.[1]',
null);"
+ def tableName = "test_query_json_replace"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) not null,
+ `time` datetime,
+ `k` int(11)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`time`,`k`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql "insert into ${tableName} values(1,'2022-01-01 11:45:14',9);"
+ sql "insert into ${tableName} values(2,'2022-01-01 11:45:14',null);"
+ sql "insert into ${tableName} values(3,null,9);"
+ sql "insert into ${tableName} values(4,null,null);"
+ order_qt_sql1 "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 ${tableName};"
+ sql "DROP TABLE ${tableName};"
+}
diff --git
a/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_set.groovy
b/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_set.groovy
new file mode 100644
index 0000000000..263fea2192
--- /dev/null
+++
b/regression-test/suites/nereids_p0/sql_functions/json_function/test_query_json_set.groovy
@@ -0,0 +1,48 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_query_json_set", "query") {
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ qt_sql "select json_set('{\"a\": 1, \"b\": [2, 3]}', '\$', null);"
+ qt_sql "select json_set('{\"k\": [1, 2]}', '\$.k[0]', null, '\$.[1]',
null);"
+ def tableName = "test_query_json_set"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) not null,
+ `time` datetime,
+ `k` int(11)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`time`,`k`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql "insert into ${tableName} values(1,'2022-01-01 11:45:14',9);"
+ sql "insert into ${tableName} values(2,'2022-01-01 11:45:14',null);"
+ sql "insert into ${tableName} values(3,null,9);"
+ sql "insert into ${tableName} values(4,null,null);"
+ order_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};"
+ 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
new file mode 100644
index 0000000000..c885e3ae34
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_insert.groovy
@@ -0,0 +1,45 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_query_json_insert", "query") {
+ qt_sql "select json_insert('{\"a\": 1, \"b\": [2, 3]}', '\$', null);"
+ qt_sql "select json_insert('{\"k\": [1, 2]}', '\$.k[0]', null, '\$.[1]',
null);"
+ def tableName = "test_query_json_insert"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) not null,
+ `time` datetime,
+ `k` int(11)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`time`,`k`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql "insert into ${tableName} values(1,'2022-01-01 11:45:14',9);"
+ sql "insert into ${tableName} values(2,'2022-01-01 11:45:14',null);"
+ 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};"
+}
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
new file mode 100644
index 0000000000..8deeda3a23
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_replace.groovy
@@ -0,0 +1,45 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_query_json_replace", "query") {
+ qt_sql "select json_replace('{\"a\": 1, \"b\": [2, 3]}', '\$', null);"
+ qt_sql "select json_replace('{\"k\": [1, 2]}', '\$.k[0]', null, '\$.[1]',
null);"
+ def tableName = "test_query_json_set"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) not null,
+ `time` datetime,
+ `k` int(11)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`time`,`k`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql "insert into ${tableName} values(1,'2022-01-01 11:45:14',9);"
+ sql "insert into ${tableName} values(2,'2022-01-01 11:45:14',null);"
+ sql "insert into ${tableName} values(3,null,9);"
+ sql "insert into ${tableName} values(4,null,null);"
+ qt_sql1 "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 ${tableName} order by id;"
+ 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
new file mode 100644
index 0000000000..2c65ddb410
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/json_function/test_query_json_set.groovy
@@ -0,0 +1,45 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_query_json_set", "query") {
+ qt_sql "select json_set('{\"a\": 1, \"b\": [2, 3]}', '\$', null);"
+ qt_sql "select json_set('{\"k\": [1, 2]}', '\$.k[0]', null, '\$.[1]',
null);"
+ def tableName = "test_query_json_set"
+ sql "DROP TABLE IF EXISTS ${tableName}"
+ sql """
+ CREATE TABLE ${tableName} (
+ `id` int(11) not null,
+ `time` datetime,
+ `k` int(11)
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`time`,`k`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql "insert into ${tableName} values(1,'2022-01-01 11:45:14',9);"
+ sql "insert into ${tableName} values(2,'2022-01-01 11:45:14',null);"
+ 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};"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
