This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 a73b6e81042 [feature](function) support
sort_json_object_keys/normalize_json_numbers_to_double function (#56576)
a73b6e81042 is described below
commit a73b6e810422a26bf1b61a85f953e7f08928c138
Author: Mryange <[email protected]>
AuthorDate: Sun Oct 12 14:40:01 2025 +0800
[feature](function) support
sort_json_object_keys/normalize_json_numbers_to_double function (#56576)
### What problem does this PR solve?
https://github.com/apache/doris-website/pull/2927
```sql
mysql> select sort_json_object_keys(cast('{"b":123,"b":456,"a":789}' as
json));
+------------------------------------------------------------------+
| sort_json_object_keys(cast('{"b":123,"b":456,"a":789}' as json)) |
+------------------------------------------------------------------+
| {"a":789,"b":123} |
+------------------------------------------------------------------+
mysql> select cast('{"abc": 18446744073709551616}' as json) ,
normalize_json_numbers_to_double(cast('{"abc": 18446744073709551616}' as json));
+-----------------------------------------------+---------------------------------------------------------------------------------+
| cast('{"abc": 18446744073709551616}' as json) |
normalize_json_numbers_to_double(cast('{"abc": 18446744073709551616}' as json))
|
+-----------------------------------------------+---------------------------------------------------------------------------------+
| {"abc":18446744073709551616} |
{"abc":1.8446744073709552e+19}
|
+-----------------------------------------------+---------------------------------------------------------------------------------+
```
### Release note
None
### Check List (For Author)
- Test <!-- At least one of them must be included. -->
- [ ] Regression test
- [ ] Unit Test
- [ ] Manual test (add detailed scripts or steps below)
- [ ] No need to test or manual test. Explain why:
- [ ] This is a refactor/code format and no logic has been changed.
- [ ] Previous test can cover this change.
- [ ] No code files have been changed.
- [ ] Other reason <!-- Add your reason? -->
- Behavior changed:
- [ ] No.
- [ ] Yes. <!-- Explain the behavior change -->
- Does this need documentation?
- [ ] No.
- [ ] Yes. <!-- Add document PR link here. eg:
https://github.com/apache/doris-website/pull/1214 -->
### Check List (For Reviewer who merge this PR)
- [ ] Confirm the release note
- [ ] Confirm test cases
- [ ] Confirm document
- [ ] Add branch pick label <!-- Add branch pick label that this PR
should merge into -->
---
be/src/vec/functions/function_jsonb_transform.cpp | 188 +++++++++++++++++++++
be/src/vec/functions/simple_function_factory.h | 2 +
.../doris/catalog/BuiltinScalarFunctions.java | 6 +
.../scalar/NormalizeJsonNumbersToDouble.java | 72 ++++++++
.../functions/scalar/SortJsonbObjectKeys.java | 72 ++++++++
.../expressions/visitor/ScalarFunctionVisitor.java | 10 ++
.../test_json_normalize_json_numbers_to_double.out | 46 +++++
.../jsonb_p0/test_json_sort_json_object_keys.out | 37 ++++
...st_json_normalize_json_numbers_to_double.groovy | 113 +++++++++++++
.../test_json_sort_json_object_keys.groovy | 100 +++++++++++
10 files changed, 646 insertions(+)
diff --git a/be/src/vec/functions/function_jsonb_transform.cpp
b/be/src/vec/functions/function_jsonb_transform.cpp
new file mode 100644
index 00000000000..17ef600c48a
--- /dev/null
+++ b/be/src/vec/functions/function_jsonb_transform.cpp
@@ -0,0 +1,188 @@
+// 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.
+
+#include <vector>
+
+#include "runtime/primitive_type.h"
+#include "util/jsonb_document.h"
+#include "util/jsonb_document_cast.h"
+#include "util/jsonb_writer.h"
+#include "vec/data_types/data_type_jsonb.h"
+#include "vec/functions/simple_function_factory.h"
+
+namespace doris::vectorized {
+
+// Sort the keys of the JSON object and deduplicate the repeated keys, keeping
the first one
+void sort_json_object_keys(JsonbWriter& jsonb_writer, const JsonbValue*
jsonb_value) {
+ if (jsonb_value->isObject()) {
+ std::vector<std::pair<StringRef, const JsonbValue*>> kvs;
+ const auto* obj_val = jsonb_value->unpack<ObjectVal>();
+ for (auto it = obj_val->begin(); it != obj_val->end(); ++it) {
+ kvs.emplace_back(StringRef(it->getKeyStr(), it->klen()),
it->value());
+ }
+ // sort by key
+ std::sort(kvs.begin(), kvs.end(),
+ [](const auto& left, const auto& right) { return left.first
< right.first; });
+ // unique by key
+ kvs.erase(std::unique(kvs.begin(), kvs.end(),
+ [](const auto& left, const auto& right) {
+ return left.first == right.first;
+ }),
+ kvs.end());
+ jsonb_writer.writeStartObject();
+ for (const auto& kv : kvs) {
+ jsonb_writer.writeKey(kv.first.data,
static_cast<uint8_t>(kv.first.size));
+ sort_json_object_keys(jsonb_writer, kv.second);
+ }
+ jsonb_writer.writeEndObject();
+ } else if (jsonb_value->isArray()) {
+ const auto* array_val = jsonb_value->unpack<ArrayVal>();
+ jsonb_writer.writeStartArray();
+ for (auto it = array_val->begin(); it != array_val->end(); ++it) {
+ sort_json_object_keys(jsonb_writer, &*it);
+ }
+ jsonb_writer.writeEndArray();
+ } else {
+ // scalar value
+ jsonb_writer.writeValue(jsonb_value);
+ }
+}
+
+// Convert all numeric types in JSON to double type
+void normalize_json_numbers_to_double(JsonbWriter& jsonb_writer, const
JsonbValue* jsonb_value) {
+ if (jsonb_value->isObject()) {
+ jsonb_writer.writeStartObject();
+ const auto* obj_val = jsonb_value->unpack<ObjectVal>();
+ for (auto it = obj_val->begin(); it != obj_val->end(); ++it) {
+ jsonb_writer.writeKey(it->getKeyStr(), it->klen());
+ normalize_json_numbers_to_double(jsonb_writer, it->value());
+ }
+ jsonb_writer.writeEndObject();
+ } else if (jsonb_value->isArray()) {
+ const auto* array_val = jsonb_value->unpack<ArrayVal>();
+ jsonb_writer.writeStartArray();
+ for (auto it = array_val->begin(); it != array_val->end(); ++it) {
+ normalize_json_numbers_to_double(jsonb_writer, &*it);
+ }
+ jsonb_writer.writeEndArray();
+ } else {
+ // scalar value
+ if (jsonb_value->isInt() || jsonb_value->isFloat() ||
jsonb_value->isDouble() ||
+ jsonb_value->isDecimal()) {
+ double to;
+ CastParameters params;
+ params.is_strict = false;
+ JsonbCast::cast_from_json_to_float(jsonb_value, to, params);
+ if (to == 0.0) {
+ // to avoid -0.0
+ to = 0.0;
+ } else if (std::isnan(to)) {
+ // to avoid -nan
+ to = std::numeric_limits<double>::quiet_NaN();
+ }
+ jsonb_writer.writeDouble(to);
+ } else {
+ jsonb_writer.writeValue(jsonb_value);
+ }
+ }
+}
+
+// Input jsonb, output jsonb
+template <typename Impl>
+class FunctionJsonbTransform : public IFunction {
+public:
+ static constexpr auto name = Impl::name;
+
+ static FunctionPtr create() { return
std::make_shared<FunctionJsonbTransform>(); }
+
+ String get_name() const override { return name; }
+
+ DataTypePtr get_return_type_impl(const DataTypes& arguments) const
override {
+ return std::make_shared<DataTypeJsonb>();
+ }
+
+ size_t get_number_of_arguments() const override { return 1; }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ uint32_t result, size_t size) const override {
+ auto input_column = block.get_by_position(arguments[0]).column;
+ auto to_column = ColumnString::create();
+
+ const auto& input_jsonb_column = assert_cast<const
ColumnString&>(*input_column);
+
+ to_column->get_chars().reserve(input_jsonb_column.get_chars().size());
+
to_column->get_offsets().reserve(input_jsonb_column.get_offsets().size());
+
+ JsonbWriter writer;
+ for (size_t i = 0; i < size; ++i) {
+ StringRef val = input_jsonb_column.get_data_at(i);
+ JsonbDocument* doc = nullptr;
+ auto st = JsonbDocument::checkAndCreateDocument(val.data,
val.size, &doc);
+ if (!st.ok() || !doc || !doc->getValue()) [[unlikely]] {
+ // mayby be invalid jsonb, just insert default
+ // invalid jsonb value may be caused by the default null
processing
+ // insert empty string
+ to_column->insert_default();
+ continue;
+ }
+ JsonbValue* value = doc->getValue();
+ if (UNLIKELY(!value)) {
+ // mayby be invalid jsonb, just insert default
+ // invalid jsonb value may be caused by the default null
processing
+ // insert empty string
+ to_column->insert_default();
+ continue;
+ }
+
+ writer.reset();
+
+ Impl::transform(writer, value);
+
+ to_column->insert_data(writer.getOutput()->getBuffer(),
writer.getOutput()->getSize());
+ }
+ block.get_by_position(result).column = std::move(to_column);
+ return Status::OK();
+ }
+};
+
+struct SortJsonObjectKeys {
+ static constexpr auto name = "sort_json_object_keys";
+ static void transform(JsonbWriter& writer, const JsonbValue* value) {
+ sort_json_object_keys(writer, value);
+ }
+};
+
+struct NormalizeJsonNumbersToDouble {
+ static constexpr auto name = "normalize_json_numbers_to_double";
+ static void transform(JsonbWriter& writer, const JsonbValue* value) {
+ normalize_json_numbers_to_double(writer, value);
+ }
+};
+
+using FunctionSortJsonObjectKeys = FunctionJsonbTransform<SortJsonObjectKeys>;
+using FunctionNormalizeJsonNumbersToDouble =
FunctionJsonbTransform<NormalizeJsonNumbersToDouble>;
+
+void register_function_json_transform(SimpleFunctionFactory& factory) {
+ factory.register_function<FunctionSortJsonObjectKeys>();
+ factory.register_function<FunctionNormalizeJsonNumbersToDouble>();
+
+ factory.register_alias(FunctionSortJsonObjectKeys::name,
"sort_jsonb_object_keys");
+ factory.register_alias(FunctionNormalizeJsonNumbersToDouble::name,
+ "normalize_jsonb_numbers_to_double");
+}
+
+} // namespace doris::vectorized
diff --git a/be/src/vec/functions/simple_function_factory.h
b/be/src/vec/functions/simple_function_factory.h
index 2b39d180204..7ff72bb60f9 100644
--- a/be/src/vec/functions/simple_function_factory.h
+++ b/be/src/vec/functions/simple_function_factory.h
@@ -76,6 +76,7 @@ void register_function_utility(SimpleFunctionFactory&
factory);
void register_function_json(SimpleFunctionFactory& factory);
void register_function_jsonb(SimpleFunctionFactory& factory);
void register_function_to_json(SimpleFunctionFactory& factory);
+void register_function_json_transform(SimpleFunctionFactory& factory);
void register_function_hash(SimpleFunctionFactory& factory);
void register_function_ifnull(SimpleFunctionFactory& factory);
void register_function_like(SimpleFunctionFactory& factory);
@@ -343,6 +344,7 @@ public:
register_function_score(instance);
register_function_binary(instance);
register_function_soundex(instance);
+ register_function_json_transform(instance);
#if defined(BE_TEST) && !defined(BE_BENCHMARK)
register_function_throw_exception(instance);
#endif
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 541d6e0a0d2..4c125b8f2f9 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
@@ -354,6 +354,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.NextDay;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NgramSearch;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NormalCdf;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.NormalizeJsonNumbersToDouble;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.NotNullOrEmpty;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Now;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NullIf;
@@ -421,6 +422,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Sm3;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sm3sum;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sm4Decrypt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sm4Encrypt;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.SortJsonbObjectKeys;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Soundex;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Space;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SplitByChar;
@@ -858,6 +860,8 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(NextDay.class, "next_day"),
scalar(NonNullable.class, "non_nullable"),
scalar(NormalCdf.class, "normal_cdf"),
+ scalar(NormalizeJsonNumbersToDouble.class,
"normalize_json_numbers_to_double"),
+ scalar(NormalizeJsonNumbersToDouble.class,
"normalize_jsonb_numbers_to_double"),
scalar(NotNullOrEmpty.class, "not_null_or_empty"),
scalar(NgramSearch.class, "ngram_search"),
scalar(Now.class, "now", "current_timestamp", "localtime",
"localtimestamp"),
@@ -932,6 +936,8 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(Sm3sum.class, "sm3sum"),
scalar(Sm4Decrypt.class, "sm4_decrypt"),
scalar(Sm4Encrypt.class, "sm4_encrypt"),
+ scalar(SortJsonbObjectKeys.class, "sort_json_object_keys"),
+ scalar(SortJsonbObjectKeys.class, "sort_jsonb_object_keys"),
scalar(Soundex.class, "soundex"),
scalar(Space.class, "space"),
scalar(SplitByChar.class, "split_by_char"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/NormalizeJsonNumbersToDouble.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/NormalizeJsonNumbersToDouble.java
new file mode 100644
index 00000000000..1ed12f926a8
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/NormalizeJsonNumbersToDouble.java
@@ -0,0 +1,72 @@
+// 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.shape.UnaryExpression;
+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 'normalize_json_numbers_to_double'.
+ */
+public class NormalizeJsonNumbersToDouble extends ScalarFunction
+ implements UnaryExpression, ExplicitlyCastableSignature,
PropagateNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(JsonType.INSTANCE).args(JsonType.INSTANCE));
+
+ /**
+ * constructor with 1 argument.
+ */
+ public NormalizeJsonNumbersToDouble(Expression arg0) {
+ super("normalize_json_numbers_to_double", arg0);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private NormalizeJsonNumbersToDouble(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public NormalizeJsonNumbersToDouble withChildren(List<Expression>
children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new NormalizeJsonNumbersToDouble(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitNormalizeJsonNumbersToDouble(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SortJsonbObjectKeys.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SortJsonbObjectKeys.java
new file mode 100644
index 00000000000..a964e0ca572
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SortJsonbObjectKeys.java
@@ -0,0 +1,72 @@
+// 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.shape.UnaryExpression;
+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 'sort_json_object_keys'.
+ */
+public class SortJsonbObjectKeys extends ScalarFunction
+ implements UnaryExpression, ExplicitlyCastableSignature,
PropagateNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(JsonType.INSTANCE).args(JsonType.INSTANCE));
+
+ /**
+ * constructor with 1 argument.
+ */
+ public SortJsonbObjectKeys(Expression arg0) {
+ super("sort_json_object_keys", arg0);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private SortJsonbObjectKeys(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public SortJsonbObjectKeys withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new SortJsonbObjectKeys(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitSortJsonbObjectKeys(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 f2a11017a71..d327bade315 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
@@ -357,6 +357,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.NextDay;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NgramSearch;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NonNullable;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NormalCdf;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.NormalizeJsonNumbersToDouble;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.NotNullOrEmpty;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Now;
import org.apache.doris.nereids.trees.expressions.functions.scalar.NullIf;
@@ -421,6 +422,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Sm3;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sm3sum;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sm4Decrypt;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Sm4Encrypt;
+import
org.apache.doris.nereids.trees.expressions.functions.scalar.SortJsonbObjectKeys;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Soundex;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Space;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SplitByChar;
@@ -1597,6 +1599,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(jsonbValid, context);
}
+ default R visitSortJsonbObjectKeys(SortJsonbObjectKeys sort, C context) {
+ return visitScalarFunction(sort, context);
+ }
+
default R visitL1Distance(L1Distance l1Distance, C context) {
return visitScalarFunction(l1Distance, context);
}
@@ -1785,6 +1791,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(normalCdf, context);
}
+ default R visitNormalizeJsonNumbersToDouble(NormalizeJsonNumbersToDouble
func, C context) {
+ return visitScalarFunction(func, context);
+ }
+
default R visitNotNullOrEmpty(NotNullOrEmpty notNullOrEmpty, C context) {
return visitScalarFunction(notNullOrEmpty, context);
}
diff --git
a/regression-test/data/jsonb_p0/test_json_normalize_json_numbers_to_double.out
b/regression-test/data/jsonb_p0/test_json_normalize_json_numbers_to_double.out
new file mode 100644
index 00000000000..bfd85dc9599
--- /dev/null
+++
b/regression-test/data/jsonb_p0/test_json_normalize_json_numbers_to_double.out
@@ -0,0 +1,46 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+{"b":1.2345678901234568e+18,"b":456,"a":789} {"b":123,"b":456,"a":789}
+
+-- !sql --
+[{"b":123,"b":456,"a":1.2345678901234568e+18},{"b":1.2133798178293133e+18},{"b":4.5612312312312314e+17},{"a":74124123132189}]
+
+-- !sql --
+nan
+
+-- !sql --
+0
+
+-- !sql --
+1231231322.23123
+
+-- !sql --
+1 {"b":123,"b":456,"a":789}
+2 {"d":123,"c":456,"b":789,"a":1011}
+3 {"x":123,"y":456,"z":789}
+4 [{"b":123,"b":456,"a":789},{"b":123},{"b":456},{"a":789}]
+5 \N
+6
{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}
+
+-- !sql --
+1 {"b":123,"b":456,"a":789}
+2 {"d":123,"c":456,"b":789,"a":1011}
+3 {"x":123,"y":456,"z":789}
+4 [{"b":123,"b":456,"a":789},{"b":123},{"b":456},{"a":789}]
+5 \N
+6
{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}
+
+-- !sql --
+1 {"b":123,"b":456,"a":789}
+2 {"d":123,"c":456,"b":789,"a":1011}
+3 {"x":123,"y":456,"z":789}
+4 [{"b":123,"b":456,"a":789},{"b":123},{"b":456},{"a":789}]
+6
{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}
+
+-- !sql --
+1 {"b":123,"b":456,"a":789}
+2 {"d":123,"c":456,"b":789,"a":1011}
+3 {"x":123,"y":456,"z":789}
+4 [{"b":123,"b":456,"a":789},{"b":123},{"b":456},{"a":789}]
+6
{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}
+
diff --git a/regression-test/data/jsonb_p0/test_json_sort_json_object_keys.out
b/regression-test/data/jsonb_p0/test_json_sort_json_object_keys.out
new file mode 100644
index 00000000000..c0ce45638fe
--- /dev/null
+++ b/regression-test/data/jsonb_p0/test_json_sort_json_object_keys.out
@@ -0,0 +1,37 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+{"a":789,"b":123} {"a":789,"b":123}
+
+-- !sql --
+[{"a":789,"b":123},{"b":123},{"b":456},{"a":789}]
+
+-- !sql --
+1 {"a":789,"b":123}
+2 {"a":1011,"b":789,"c":456,"d":123}
+3 {"x":123,"y":456,"z":789}
+4 [{"a":789,"b":123},{"b":123},{"b":456},{"a":789}]
+5 \N
+6
{"Data":[{"A_1":"anotherA_1","B2":{"A_1":"value2","__key__":[{"1dupKey":"fourth","Dupkey":"second","dupKey":"first"},{"Mix_Key":"bar","mix_key":"foo"}],"b2":"should_sort_last","zZ":123},"a_1":"value1","b2":"duplicateB2"},{"b2":[{"K":2,"k":1},{"B2":"array_B2","b2":"array_dup"}],"randomKey_3":{"Foo":"TEST","foo":"test"}}],"meta_9":{"Info":"EXAMPLE","info":"example"}}
+
+-- !sql --
+1 {"a":789,"b":123}
+2 {"a":1011,"b":789,"c":456,"d":123}
+3 {"x":123,"y":456,"z":789}
+4 [{"a":789,"b":123},{"b":123},{"b":456},{"a":789}]
+5 \N
+6
{"Data":[{"A_1":"anotherA_1","B2":{"A_1":"value2","__key__":[{"1dupKey":"fourth","Dupkey":"second","dupKey":"first"},{"Mix_Key":"bar","mix_key":"foo"}],"b2":"should_sort_last","zZ":123},"a_1":"value1","b2":"duplicateB2"},{"b2":[{"K":2,"k":1},{"B2":"array_B2","b2":"array_dup"}],"randomKey_3":{"Foo":"TEST","foo":"test"}}],"meta_9":{"Info":"EXAMPLE","info":"example"}}
+
+-- !sql --
+1 {"a":789,"b":123}
+2 {"a":1011,"b":789,"c":456,"d":123}
+3 {"x":123,"y":456,"z":789}
+4 [{"a":789,"b":123},{"b":123},{"b":456},{"a":789}]
+6
{"Data":[{"A_1":"anotherA_1","B2":{"A_1":"value2","__key__":[{"1dupKey":"fourth","Dupkey":"second","dupKey":"first"},{"Mix_Key":"bar","mix_key":"foo"}],"b2":"should_sort_last","zZ":123},"a_1":"value1","b2":"duplicateB2"},{"b2":[{"K":2,"k":1},{"B2":"array_B2","b2":"array_dup"}],"randomKey_3":{"Foo":"TEST","foo":"test"}}],"meta_9":{"Info":"EXAMPLE","info":"example"}}
+
+-- !sql --
+1 {"a":789,"b":123}
+2 {"a":1011,"b":789,"c":456,"d":123}
+3 {"x":123,"y":456,"z":789}
+4 [{"a":789,"b":123},{"b":123},{"b":456},{"a":789}]
+6
{"Data":[{"A_1":"anotherA_1","B2":{"A_1":"value2","__key__":[{"1dupKey":"fourth","Dupkey":"second","dupKey":"first"},{"Mix_Key":"bar","mix_key":"foo"}],"b2":"should_sort_last","zZ":123},"a_1":"value1","b2":"duplicateB2"},{"b2":[{"K":2,"k":1},{"B2":"array_B2","b2":"array_dup"}],"randomKey_3":{"Foo":"TEST","foo":"test"}}],"meta_9":{"Info":"EXAMPLE","info":"example"}}
+
diff --git
a/regression-test/suites/jsonb_p0/test_json_normalize_json_numbers_to_double.groovy
b/regression-test/suites/jsonb_p0/test_json_normalize_json_numbers_to_double.groovy
new file mode 100644
index 00000000000..0736fb4a8c7
--- /dev/null
+++
b/regression-test/suites/jsonb_p0/test_json_normalize_json_numbers_to_double.groovy
@@ -0,0 +1,113 @@
+// 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_json_normalize_json_numbers_to_double") {
+ qt_sql """
+ select
normalize_json_numbers_to_double(cast('{"b":1234567890123456789,"b":456,"a":789}'
as json)) ,
normalize_jsonb_numbers_to_double(cast('{"b":123,"b":456,"a":789}' as json));
+ """
+
+
+ qt_sql """
+ select
normalize_json_numbers_to_double(cast('[{"b":123,"b":456,"a":1234567890123456789.231231}
,{"b":1213379817829313213},{"b":456123123123123123},{"a":74124123132189} ]' as
json));
+ """
+
+
+ qt_sql """
+ select normalize_json_numbers_to_double(to_json(cast('nan' as
double)) );
+ """
+
+ qt_sql """
+ select normalize_json_numbers_to_double(to_json(cast('-0.0' as
double)) );
+ """
+
+ qt_sql """
+ select
normalize_json_numbers_to_double(to_json(cast('1231231322.23123' as Decimal)) );
+ """
+
+ sql "DROP TABLE IF EXISTS test_json_normalize_json_numbers_to_double_table"
+
+ sql """
+ CREATE TABLE IF NOT EXISTS
test_json_normalize_json_numbers_to_double_table (
+ id INT,
+ j JSON
+ )
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """
+ INSERT INTO test_json_normalize_json_numbers_to_double_table VALUES
+ (1, '{"b":123,"b":456,"a":789}'),
+ (2, '{"d":123,"c":456,"b":789,"a":1011}'),
+ (3, '{"x":123,"y":456,"z":789}'),
+ (4, '[{"b":123,"b":456,"a":789} ,{"b":123},{"b":456},{"a":789}]'),
+ (5, null),
+ (6,
'{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}')
+ """
+
+ qt_sql """
+ SELECT id, normalize_json_numbers_to_double(j) as sorted_json
+ FROM test_json_normalize_json_numbers_to_double_table
+ ORDER BY id
+ """
+
+ qt_sql """
+ SELECT id, normalize_jsonb_numbers_to_double(cast(j as jsonb)) as
sorted_jsonb
+ FROM test_json_normalize_json_numbers_to_double_table
+ ORDER BY id
+ """
+
+
+
+ sql "DROP TABLE IF EXISTS test_json_normalize_json_numbers_to_double_table"
+
+ sql """
+ CREATE TABLE IF NOT EXISTS
test_json_normalize_json_numbers_to_double_table (
+ id INT,
+ j JSON NOT NULL
+ )
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """
+ INSERT INTO test_json_normalize_json_numbers_to_double_table VALUES
+ (1, '{"b":123,"b":456,"a":789}'),
+ (2, '{"d":123,"c":456,"b":789,"a":1011}'),
+ (3, '{"x":123,"y":456,"z":789}'),
+ (4, '[{"b":123,"b":456,"a":789} ,{"b":123},{"b":456},{"a":789}]'),
+ (6,
'{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}')
+ """
+
+ qt_sql """
+ SELECT id, normalize_json_numbers_to_double(j) as sorted_json
+ FROM test_json_normalize_json_numbers_to_double_table
+ ORDER BY id
+ """
+
+ qt_sql """
+ SELECT id, normalize_jsonb_numbers_to_double(cast(j as jsonb)) as
sorted_jsonb
+ FROM test_json_normalize_json_numbers_to_double_table
+ ORDER BY id
+ """
+
+}
diff --git
a/regression-test/suites/jsonb_p0/test_json_sort_json_object_keys.groovy
b/regression-test/suites/jsonb_p0/test_json_sort_json_object_keys.groovy
new file mode 100644
index 00000000000..65f8f3f4b37
--- /dev/null
+++ b/regression-test/suites/jsonb_p0/test_json_sort_json_object_keys.groovy
@@ -0,0 +1,100 @@
+// 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_json_sort_json_object_keys") {
+ qt_sql """
+ select sort_json_object_keys(cast('{"b":123,"b":456,"a":789}' as
json)) , sort_jsonb_object_keys(cast('{"b":123,"b":456,"a":789}' as json));
+ """
+
+
+ qt_sql """
+ select sort_json_object_keys(cast('[{"b":123,"b":456,"a":789}
,{"b":123},{"b":456},{"a":789} ]' as json));
+ """
+
+ sql "DROP TABLE IF EXISTS test_json_sort_json_object_keys_table"
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_json_sort_json_object_keys_table (
+ id INT,
+ j JSON
+ )
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """
+ INSERT INTO test_json_sort_json_object_keys_table VALUES
+ (1, '{"b":123,"b":456,"a":789}'),
+ (2, '{"d":123,"c":456,"b":789,"a":1011}'),
+ (3, '{"x":123,"y":456,"z":789}'),
+ (4, '[{"b":123,"b":456,"a":789} ,{"b":123},{"b":456},{"a":789}]'),
+ (5, null),
+ (6,
'{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}')
+ """
+
+ qt_sql """
+ SELECT id, sort_json_object_keys(j) as sorted_json
+ FROM test_json_sort_json_object_keys_table
+ ORDER BY id
+ """
+
+ qt_sql """
+ SELECT id, sort_jsonb_object_keys(cast(j as jsonb)) as sorted_jsonb
+ FROM test_json_sort_json_object_keys_table
+ ORDER BY id
+ """
+
+
+
+ sql "DROP TABLE IF EXISTS test_json_sort_json_object_keys_table"
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_json_sort_json_object_keys_table (
+ id INT,
+ j JSON NOT NULL
+ )
+ DISTRIBUTED BY HASH(id) BUCKETS 3
+ PROPERTIES (
+ "replication_num" = "1"
+ )
+ """
+
+ sql """
+ INSERT INTO test_json_sort_json_object_keys_table VALUES
+ (1, '{"b":123,"b":456,"a":789}'),
+ (2, '{"d":123,"c":456,"b":789,"a":1011}'),
+ (3, '{"x":123,"y":456,"z":789}'),
+ (4, '[{"b":123,"b":456,"a":789} ,{"b":123},{"b":456},{"a":789}]'),
+ (6,
'{"Data":[{"a_1":"value1","B2":{"zZ":123,"A_1":"value2","b2":"should_sort_last","b2":"should_sort_first","__key__":[{"dupKey":"first","Dupkey":"second","dupKey":"third","1dupKey":"fourth"},{"mix_key":"foo","Mix_Key":"bar","mix_key":"baz"}]},"B2":"anotherB2","b2":"duplicateB2","A_1":"anotherA_1"},{"b2":[{"k":1,"K":2,"k":3},{"b2":"array_dup","B2":"array_B2"}],"randomKey_3":{"foo":"test","Foo":"TEST","foo":"again"}}],"meta_9":{"info":"example","Info":"EXAMPLE","info":"duplicate"}}')
+ """
+
+ qt_sql """
+ SELECT id, sort_json_object_keys(j) as sorted_json
+ FROM test_json_sort_json_object_keys_table
+ ORDER BY id
+ """
+
+ qt_sql """
+ SELECT id, sort_jsonb_object_keys(cast(j as jsonb)) as sorted_jsonb
+ FROM test_json_sort_json_object_keys_table
+ ORDER BY id
+ """
+
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
