This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new a85f3466188 branch-4.0: [feat](function) Add new function of
strip_null_value #57386 (#57689)
a85f3466188 is described below
commit a85f346618818854035f00c8698121ccce6e7204
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Tue Nov 4 21:32:27 2025 +0800
branch-4.0: [feat](function) Add new function of strip_null_value #57386
(#57689)
Cherry-picked from #57386
Co-authored-by: Jerry Hu <[email protected]>
---
be/src/vec/functions/function_jsonb.cpp | 67 ++++++++++++++++
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../functions/scalar/StripNullValue.java | 67 ++++++++++++++++
.../expressions/visitor/ScalarFunctionVisitor.java | 5 ++
.../test_strip_null_value.out | 28 +++++++
.../test_strip_null_value.groovy | 88 ++++++++++++++++++++++
6 files changed, 257 insertions(+)
diff --git a/be/src/vec/functions/function_jsonb.cpp
b/be/src/vec/functions/function_jsonb.cpp
index aaf6a2f2295..c4ab4efef81 100644
--- a/be/src/vec/functions/function_jsonb.cpp
+++ b/be/src/vec/functions/function_jsonb.cpp
@@ -3028,6 +3028,71 @@ private:
}
};
+class FunctionStripNullValue : public IFunction {
+public:
+ static constexpr auto name = "strip_null_value";
+ static FunctionPtr create() { return
std::make_shared<FunctionStripNullValue>(); }
+
+ String get_name() const override { return name; }
+ bool is_variadic() const override { return false; }
+ size_t get_number_of_arguments() const override { return 1; }
+
+ bool use_default_implementation_for_nulls() const override { return false;
}
+
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ return make_nullable(std::make_shared<DataTypeJsonb>());
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ uint32_t result, size_t input_rows_count) const
override {
+ const auto& arg_column = block.get_by_position(arguments[0]).column;
+ const ColumnString* json_column = nullptr;
+ const NullMap* json_null_map = nullptr;
+ if (arg_column->is_nullable()) {
+ const auto& nullable_col = assert_cast<const
ColumnNullable&>(*arg_column);
+ json_column = assert_cast<const
ColumnString*>(&nullable_col.get_nested_column());
+ json_null_map = &nullable_col.get_null_map_data();
+ } else {
+ json_column = assert_cast<const ColumnString*>(arg_column.get());
+ }
+
+ auto return_data_type =
make_nullable(std::make_shared<DataTypeJsonb>());
+ auto result_column = return_data_type->create_column();
+
+ auto& result_nullmap =
assert_cast<ColumnNullable&>(*result_column).get_null_map_data();
+ auto& result_data_col = assert_cast<ColumnString&>(
+
assert_cast<ColumnNullable&>(*result_column).get_nested_column());
+
+ result_nullmap.resize_fill(input_rows_count, 0);
+ for (size_t i = 0; i != input_rows_count; ++i) {
+ if (json_null_map && (*json_null_map)[i]) {
+ result_nullmap[i] = 1;
+ result_data_col.insert_default();
+ continue;
+ }
+ JsonbDocument* json_doc = nullptr;
+ const auto& json_str = json_column->get_data_at(i);
+ RETURN_IF_ERROR(
+ JsonbDocument::checkAndCreateDocument(json_str.data,
json_str.size, &json_doc));
+ if (json_doc) [[likely]] {
+ if (json_doc->getValue()->isNull()) {
+ result_nullmap[i] = 1;
+ result_data_col.insert_default();
+ } else {
+ result_nullmap[i] = 0;
+ result_data_col.insert_data(json_str.data, json_str.size);
+ }
+ } else {
+ result_nullmap[i] = 1;
+ result_data_col.insert_default();
+ }
+ }
+
+ block.get_by_position(result).column = std::move(result_column);
+ return Status::OK();
+ }
+};
+
void register_function_jsonb(SimpleFunctionFactory& factory) {
factory.register_function<FunctionJsonbParse>(FunctionJsonbParse::name);
factory.register_alias(FunctionJsonbParse::name,
FunctionJsonbParse::alias);
@@ -3079,6 +3144,8 @@ void register_function_jsonb(SimpleFunctionFactory&
factory) {
factory.register_function<FunctionJsonbRemove>();
factory.register_alias(FunctionJsonbRemove::name,
FunctionJsonbRemove::alias);
+
+ factory.register_function<FunctionStripNullValue>();
}
} // namespace doris::vectorized
\ No newline at end of file
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 cefc5dc6eb5..8b5ea5e5b79 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
@@ -468,6 +468,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.StartsWith;
import org.apache.doris.nereids.trees.expressions.functions.scalar.StrToDate;
import org.apache.doris.nereids.trees.expressions.functions.scalar.StrToMap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Strcmp;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.StripNullValue;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.StructElement;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBinary;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBitmap;
@@ -1009,6 +1010,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(StY.class, "st_y"),
scalar(StartsWith.class, "starts_with"),
scalar(Strcmp.class, "strcmp"),
+ scalar(StripNullValue.class, "strip_null_value"),
scalar(StrToDate.class, "str_to_date"),
scalar(StrToMap.class, "str_to_map"),
scalar(SubBinary.class, "sub_binary"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StripNullValue.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StripNullValue.java
new file mode 100644
index 00000000000..58dbfed4c3b
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/StripNullValue.java
@@ -0,0 +1,67 @@
+// 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.AlwaysNullable;
+import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.JsonType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'strip_null_value'.
+ */
+public class StripNullValue extends ScalarFunction implements
ExplicitlyCastableSignature, AlwaysNullable {
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(JsonType.INSTANCE).args(JsonType.INSTANCE)
+ );
+
+ public StripNullValue(Expression arg) {
+ super("strip_null_value", arg);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private StripNullValue(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public StripNullValue withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new StripNullValue(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitStripNullValue(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 5e4ce74823f..4727d9a773f 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
@@ -469,6 +469,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.StartsWith;
import org.apache.doris.nereids.trees.expressions.functions.scalar.StrToDate;
import org.apache.doris.nereids.trees.expressions.functions.scalar.StrToMap;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Strcmp;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.StripNullValue;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.StructElement;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBinary;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SubBitmap;
@@ -2432,6 +2433,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(strcmp, context);
}
+ default R visitStripNullValue(StripNullValue stripNullValue, C context) {
+ return visitScalarFunction(stripNullValue, context);
+ }
+
default R visitVersion(Version version, C context) {
return visitScalarFunction(version, context);
}
diff --git
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_strip_null_value.out
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_strip_null_value.out
new file mode 100644
index 00000000000..e282f5526c7
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_strip_null_value.out
@@ -0,0 +1,28 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !test --
+1 "null" 30
+2 "Bob" \N
+3 \N \N
+4 \N \N
+
+-- !test2 --
+1 "Alice2" \N
+2 "Bob" \N
+3 "Jack" 28
+4 "Jim" 33
+
+-- !test2 --
+1 "a" "a"
+2 \N \N
+3 \N "c"
+4 \N \N
+
+-- !test3 --
+1 "aaa" 123 "aaa" 123
+2 "bbbb" "a123" "bbbb" "a123"
+3 \N \N \N \N
+4 \N \N \N 7890
+
+-- !const --
+"aaa" \N "ccc" \N \N
+
diff --git
a/regression-test/suites/query_p0/sql_functions/conditional_functions/test_strip_null_value.groovy
b/regression-test/suites/query_p0/sql_functions/conditional_functions/test_strip_null_value.groovy
new file mode 100644
index 00000000000..6308e318dc8
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/conditional_functions/test_strip_null_value.groovy
@@ -0,0 +1,88 @@
+// 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_strip_null_value") {
+ sql """DROP TABLE IF EXISTS `test_strip_null_value_table`;"""
+ sql """CREATE TABLE test_strip_null_value_table (
+ id INT,
+ json_value JSON,
+ json_value_non_null JSON not null
+ ) PROPERTIES ("replication_num"="1");"""
+
+ sql """INSERT INTO test_strip_null_value_table VALUES
+ (1, '{"name": "null", "age": 30, "a": "aaa", "b": "b", "c": null}',
'{"name": "Alice2", "age": null, "a": 123, "c": null}'),
+ (2, '{"name": "Bob", "age": null, "b": "bbbb", "c": 23423, "d":
null}', '{"name": "Bob", "age": null, "b": "a123", "c": null, "d": 9993}'),
+ (3, null, '{"name": "Jack", "age": 28, "a": null, "b": null, "c":
null}'),
+ (4, null, '{"name": "Jim", "age": 33, "a": 1234, "b": 4567, "d":
7890}');
+ """
+
+ qt_test """
+ select id,
+ strip_null_value(json_extract(json_value, '\$.name')) striped,
+ strip_null_value(json_extract(json_value, '\$.age')) as striped2
+ from test_strip_null_value_table order by 1;
+ """
+
+ qt_test2 """
+ select id,
+ strip_null_value(json_extract(json_value_non_null, '\$.name'))
striped,
+ strip_null_value(json_extract(json_value_non_null, '\$.age'))
striped2
+ from test_strip_null_value_table order by 1;
+ """
+
+ sql """DROP TABLE IF EXISTS `test_strip_null_value_paths_table`;"""
+ sql """CREATE TABLE test_strip_null_value_paths_table (
+ id INT,
+ path string,
+ path_not_null string not null
+ ) PROPERTIES ("replication_num"="1");"""
+
+ sql """INSERT INTO test_strip_null_value_paths_table VALUES
+ (1, '\$.a', '\$.a'),
+ (2, '\$.b', '\$.b'),
+ (3, null, '\$.c'),
+ (4, null, '\$.d');
+ """
+
+ qt_test2 """
+ select
+ id,
+ strip_null_value(json_extract('{"a": "a", "b": null, "c": "c",
"d": null}', path)) striped1,
+ strip_null_value(json_extract('{"a": "a", "b": null, "c": "c",
"d": null}', path_not_null)) striped2
+ from test_strip_null_value_paths_table order by 1;
+ """
+
+ qt_test3 """
+ select
+ t1.id,
+ strip_null_value(json_extract(t1.json_value, t2.path)) striped1,
+ strip_null_value(json_extract(t1.json_value_non_null, t2.path))
striped2,
+ strip_null_value(json_extract(t1.json_value, t2.path_not_null))
striped3,
+ strip_null_value(json_extract(t1.json_value_non_null,
t2.path_not_null)) striped4
+ from test_strip_null_value_table t1
+ inner join test_strip_null_value_paths_table t2 on t1.id = t2.id
+ order by t1.id;
+ """
+
+ qt_const """
+ select strip_null_value(json_extract('{"a": "aaa", "b": null, "c":
"ccc", "d": null}', '\$.a')) as striped1,
+ strip_null_value(json_extract('{"a": "aaa", "b": null, "c":
"ccc", "d": null}', '\$.b')) as striped2,
+ strip_null_value(json_extract('{"a": "aaa", "b": null, "c":
"ccc", "d": null}', '\$.c')) as striped3,
+ strip_null_value(json_extract('{"a": "aaa", "b": null, "c":
"ccc", "d": null}', '\$.d')) as striped4,
+ strip_null_value(NULL) as striped5;
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
