This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 3c3f3c25398cf597fb067c58f9200f1aae23d5e4 Author: Liqf <[email protected]> AuthorDate: Wed Sep 20 10:40:44 2023 +0800 [feature](jsonb)support json_length and json_contains function (#24332) --- be/src/util/jsonb_document.h | 148 ++++++++++- be/src/vec/functions/function_json.cpp | 9 +- be/src/vec/functions/function_jsonb.cpp | 276 ++++++++++++++++++++- .../sql-functions/json-functions/json-contains.md | 79 ++++++ .../sql-functions/json-functions/json-length.md | 68 +++++ docs/sidebars.json | 2 + .../sql-functions/json-functions/json-contains.md | 79 ++++++ .../sql-functions/json-functions/json-length.md | 68 +++++ .../doris/catalog/BuiltinScalarFunctions.java | 4 + .../expressions/functions/scalar/JsonContains.java | 86 +++++++ .../expressions/functions/scalar/JsonLength.java | 86 +++++++ .../expressions/visitor/ScalarFunctionVisitor.java | 10 + gensrc/script/doris_builtins_functions.py | 10 +- .../data/jsonb_p0/test_jsonb_load_and_function.out | 262 +++++++++++++++++++ .../test_jsonb_load_unique_key_and_function.out | 238 ++++++++++++++++++ .../json_functions/test_json_function.out | 14 +- .../jsonb_p0/test_jsonb_load_and_function.groovy | 34 +++ .../test_jsonb_load_unique_key_and_function.groovy | 33 +++ 18 files changed, 1485 insertions(+), 21 deletions(-) diff --git a/be/src/util/jsonb_document.h b/be/src/util/jsonb_document.h index eab053cfb0..3150f0d7ee 100644 --- a/be/src/util/jsonb_document.h +++ b/be/src/util/jsonb_document.h @@ -361,6 +361,8 @@ public: leg_info* get_leg_from_leg_vector(size_t i) { return leg_vector[i].get(); } + void clean() { leg_vector.clear(); } + private: std::vector<std::unique_ptr<leg_info>> leg_vector; }; @@ -544,6 +546,12 @@ public: // size of the value in bytes unsigned int size() const; + //Get the number of jsonbvalue elements + int length() const; + + //Whether to include the jsonbvalue rhs + bool contains(JsonbValue* rhs) const; + // get the raw byte array of the value const char* getValuePtr() const; @@ -897,6 +905,40 @@ public: return end(); } + // Get number of elements in object + int numElem() const { + const char* pch = payload_; + const char* fence = payload_ + size_; + + unsigned int num = 0; + while (pch < fence) { + JsonbKeyValue* pkey = (JsonbKeyValue*)(pch); + ++num; + pch += pkey->numPackedBytes(); + } + + assert(pch == fence); + + return num; + } + + JsonbKeyValue* getJsonbKeyValue(unsigned int i) const { + const char* pch = payload_; + const char* fence = payload_ + size_; + + unsigned int num = 0; + while (pch < fence) { + JsonbKeyValue* pkey = (JsonbKeyValue*)(pch); + if (num == i) return pkey; + ++num; + pch += pkey->numPackedBytes(); + } + + assert(pch == fence); + + return nullptr; + } + JsonbValue* find(const char* key, hDictFind handler = nullptr) const { return const_cast<ObjectVal*>(this)->find(key, handler); } @@ -975,7 +1017,7 @@ public: } // Get number of elements in array - unsigned int numElem() const { + int numElem() const { const char* pch = payload_; const char* fence = payload_ + size_; @@ -1195,6 +1237,110 @@ inline unsigned int JsonbValue::size() const { } } +inline int JsonbValue::length() const { + switch (type_) { + case JsonbType::T_Int8: + case JsonbType::T_Int16: + case JsonbType::T_Int32: + case JsonbType::T_Int64: + case JsonbType::T_Double: + case JsonbType::T_Float: + case JsonbType::T_Int128: + case JsonbType::T_String: + case JsonbType::T_Binary: + case JsonbType::T_Null: + case JsonbType::T_True: + case JsonbType::T_False: { + return 1; + } + case JsonbType::T_Object: { + return ((ObjectVal*)this)->numElem(); + } + case JsonbType::T_Array: { + return ((ArrayVal*)this)->numElem(); + } + default: + return 0; + } +} + +inline bool JsonbValue::contains(JsonbValue* rhs) const { + switch (type_) { + case JsonbType::T_Int8: + case JsonbType::T_Int16: + case JsonbType::T_Int32: + case JsonbType::T_Int64: + case JsonbType::T_Int128: { + return ((JsonbIntVal*)(this))->val() == ((JsonbIntVal*)(rhs))->val(); + } + case JsonbType::T_Double: { + if (rhs->isDouble()) { + return ((JsonbDoubleVal*)(this))->val() == ((JsonbDoubleVal*)(rhs))->val(); + } + return false; + } + case JsonbType::T_Float: { + if (rhs->isDouble()) { + return ((JsonbFloatVal*)(this))->val() == ((JsonbFloatVal*)(rhs))->val(); + } + return false; + } + case JsonbType::T_String: + case JsonbType::T_Binary: { + if (rhs->isString()) { + auto str_value1 = (JsonbStringVal*)this; + auto str_value2 = (JsonbStringVal*)rhs; + return str_value1->length() == str_value2->length() && + std::memcmp(str_value1->getBlob(), str_value2->getBlob(), + str_value1->length()) == 0; + } + return false; + } + case JsonbType::T_Array: { + if (rhs->isArray() && ((ArrayVal*)this)->numElem() == ((ArrayVal*)rhs)->numElem() && + ((ArrayVal*)this)->numPackedBytes() == ((ArrayVal*)rhs)->numPackedBytes()) { + for (int i = 0; i < ((ArrayVal*)this)->numElem(); ++i) { + if (!((ArrayVal*)this)->get(i)->contains(((ArrayVal*)rhs)->get(i))) { + return false; + } + } + return true; + } + for (int i = 0; i < ((ArrayVal*)this)->numElem(); ++i) { + if (((ArrayVal*)this)->get(i)->contains(rhs)) { + return true; + } + } + return false; + } + case JsonbType::T_Object: { + if (rhs->isObject()) { + auto str_value1 = (ObjectVal*)this; + auto str_value2 = (ObjectVal*)rhs; + for (int i = 0; i < str_value2->numElem(); ++i) { + JsonbKeyValue* key = str_value2->getJsonbKeyValue(i); + JsonbValue* value = str_value1->find(key->getKeyStr(), key->klen()); + if (key != nullptr && value != nullptr && !key->value()->contains(value)) + return false; + } + return true; + } + return false; + } + case JsonbType::T_Null: { + return rhs->isNull(); + } + case JsonbType::T_True: { + return rhs->isTrue(); + } + case JsonbType::T_False: { + return rhs->isFalse(); + } + default: + return false; + } +} + inline const char* JsonbValue::getValuePtr() const { switch (type_) { case JsonbType::T_Int8: diff --git a/be/src/vec/functions/function_json.cpp b/be/src/vec/functions/function_json.cpp index 12af7f4bf1..7ea820b1cf 100644 --- a/be/src/vec/functions/function_json.cpp +++ b/be/src/vec/functions/function_json.cpp @@ -949,7 +949,12 @@ public: size_t get_number_of_arguments() const override { return 3; } DataTypePtr get_return_type_impl(const DataTypes& arguments) const override { - return make_nullable(std::make_shared<DataTypeInt32>()); + return make_nullable(std::make_shared<DataTypeUInt8>()); + } + + DataTypes get_variadic_argument_types_impl() const override { + return {std::make_shared<DataTypeString>(), std::make_shared<DataTypeString>(), + std::make_shared<DataTypeString>()}; } bool use_default_implementation_for_nulls() const override { return false; } @@ -1022,7 +1027,7 @@ public: return Status::RuntimeError("Illegal column should be ColumnString"); } - auto col_to = ColumnVector<vectorized::Int32>::create(); + auto col_to = ColumnVector<vectorized::UInt8>::create(); auto& vec_to = col_to->get_data(); size_t size = col_json.size(); vec_to.resize(size); diff --git a/be/src/vec/functions/function_jsonb.cpp b/be/src/vec/functions/function_jsonb.cpp index eaedf5a69c..a354e2b23a 100644 --- a/be/src/vec/functions/function_jsonb.cpp +++ b/be/src/vec/functions/function_jsonb.cpp @@ -539,15 +539,6 @@ public: rdata.size())); } - // if not valid json path , should return error message to user - if (is_invalid_json_path) { - return Status::InvalidArgument( - "Json path error: {} for value: {}", - JsonbErrMsg::getErrMsg(JsonbErrType::E_INVALID_JSON_PATH), - std::string_view(reinterpret_cast<const char*>(rdata.data()), - rdata.size())); - } - json_path_list[pi] = std::move(path); return Status::OK(); @@ -1011,6 +1002,268 @@ using FunctionJsonbExtractDouble = FunctionJsonbExtract<JsonbExtractDouble>; using FunctionJsonbExtractString = FunctionJsonbExtract<JsonbExtractString>; using FunctionJsonbExtractJsonb = FunctionJsonbExtract<JsonbExtractJsonb>; +template <typename Impl> +class FunctionJsonbLength : public IFunction { +public: + static constexpr auto name = "json_length"; + String get_name() const override { return name; } + static FunctionPtr create() { return std::make_shared<FunctionJsonbLength<Impl>>(); } + + DataTypePtr get_return_type_impl(const DataTypes& arguments) const override { + return make_nullable(std::make_shared<DataTypeInt32>()); + } + DataTypes get_variadic_argument_types_impl() const override { + return Impl::get_variadic_argument_types(); + } + size_t get_number_of_arguments() const override { + return get_variadic_argument_types_impl().size(); + } + + bool use_default_implementation_for_nulls() const override { return false; } + + Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments, + size_t result, size_t input_rows_count) override { + return Impl::execute_impl(context, block, arguments, result, input_rows_count); + } +}; + +struct JsonbLengthUtil { + static Status jsonb_length_execute(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + DCHECK_GE(arguments.size(), 2); + ColumnPtr jsonb_data_column; + bool jsonb_data_const = false; + // prepare jsonb data column + std::tie(jsonb_data_column, jsonb_data_const) = + unpack_if_const(block.get_by_position(arguments[0]).column); + ColumnPtr path_column; + bool is_const = false; + std::tie(path_column, is_const) = + unpack_if_const(block.get_by_position(arguments[1]).column); + JsonbPath path; + if (is_const) { + auto path_value = path_column->get_data_at(0); + if (!path.seek(path_value.data, path_value.size)) { + return Status::InvalidArgument( + "Json path error: {} for value: {}", + JsonbErrMsg::getErrMsg(JsonbErrType::E_INVALID_JSON_PATH), + std::string_view(reinterpret_cast<const char*>(path_value.data), + path_value.size)); + } + } + auto null_map = ColumnUInt8::create(input_rows_count, 0); + auto return_type = block.get_data_type(result); + MutableColumnPtr res = return_type->create_column(); + + for (size_t i = 0; i < input_rows_count; ++i) { + if (jsonb_data_column->is_null_at(i) || path_column->is_null_at(i)) { + null_map->get_data()[i] = 1; + res->insert_data(nullptr, 0); + continue; + } + if (!is_const) { + auto path_value = path_column->get_data_at(i); + path.clean(); + if (!path.seek(path_value.data, path_value.size)) { + return Status::InvalidArgument( + "Json path error: {} for value: {}", + JsonbErrMsg::getErrMsg(JsonbErrType::E_INVALID_JSON_PATH), + std::string_view(reinterpret_cast<const char*>(path_value.data), + path_value.size)); + } + } + auto jsonb_value = jsonb_data_column->get_data_at(i); + // doc is NOT necessary to be deleted since JsonbDocument will not allocate memory + JsonbDocument* doc = JsonbDocument::createDocument(jsonb_value.data, jsonb_value.size); + JsonbValue* value = doc->getValue()->findValue(path, nullptr); + if (UNLIKELY(jsonb_value.size == 0 || !value)) { + null_map->get_data()[i] = 1; + res->insert_data(nullptr, 0); + continue; + } + auto length = value->length(); + res->insert_data(const_cast<const char*>((char*)&length), 0); + } + block.replace_by_position(result, + ColumnNullable::create(std::move(res), std::move(null_map))); + return Status::OK(); + } +}; + +struct JsonbLengthImpl { + static DataTypes get_variadic_argument_types() { return {std::make_shared<DataTypeJsonb>()}; } + + static Status execute_impl(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + auto path = ColumnString::create(); + std::string root_path = "$"; + + for (int i = 0; i < input_rows_count; i++) { + reinterpret_cast<ColumnString*>(path.get()) + ->insert_data(root_path.data(), root_path.size()); + } + + block.insert({std::move(path), std::make_shared<DataTypeString>(), "path"}); + ColumnNumbers temp_arguments = {arguments[0], block.columns() - 1}; + + return JsonbLengthUtil::jsonb_length_execute(context, block, temp_arguments, result, + input_rows_count); + } +}; + +struct JsonbLengthAndPathImpl { + static DataTypes get_variadic_argument_types() { + return {std::make_shared<DataTypeJsonb>(), std::make_shared<DataTypeString>()}; + } + + static Status execute_impl(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + return JsonbLengthUtil::jsonb_length_execute(context, block, arguments, result, + input_rows_count); + } +}; + +template <typename Impl> +class FunctionJsonbContains : public IFunction { +public: + static constexpr auto name = "json_contains"; + String get_name() const override { return name; } + static FunctionPtr create() { return std::make_shared<FunctionJsonbContains<Impl>>(); } + + DataTypePtr get_return_type_impl(const DataTypes& arguments) const override { + return make_nullable(std::make_shared<DataTypeUInt8>()); + } + DataTypes get_variadic_argument_types_impl() const override { + return Impl::get_variadic_argument_types(); + } + size_t get_number_of_arguments() const override { + return get_variadic_argument_types_impl().size(); + } + + bool use_default_implementation_for_nulls() const override { return false; } + + Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments, + size_t result, size_t input_rows_count) override { + return Impl::execute_impl(context, block, arguments, result, input_rows_count); + } +}; + +struct JsonbContainsUtil { + static Status jsonb_contains_execute(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + DCHECK_GE(arguments.size(), 3); + + auto jsonb_data1_column = block.get_by_position(arguments[0]).column; + auto jsonb_data2_column = block.get_by_position(arguments[1]).column; + + ColumnPtr path_column; + bool is_const = false; + std::tie(path_column, is_const) = + unpack_if_const(block.get_by_position(arguments[2]).column); + + JsonbPath path; + if (is_const) { + auto path_value = path_column->get_data_at(0); + if (!path.seek(path_value.data, path_value.size)) { + return Status::InvalidArgument( + "Json path error: {} for value: {}", + JsonbErrMsg::getErrMsg(JsonbErrType::E_INVALID_JSON_PATH), + std::string_view(reinterpret_cast<const char*>(path_value.data), + path_value.size)); + } + } + auto null_map = ColumnUInt8::create(input_rows_count, 0); + auto return_type = block.get_data_type(result); + MutableColumnPtr res = return_type->create_column(); + + for (size_t i = 0; i < input_rows_count; ++i) { + if (jsonb_data1_column->is_null_at(i) || jsonb_data2_column->is_null_at(i) || + path_column->is_null_at(i)) { + null_map->get_data()[i] = 1; + res->insert_data(nullptr, 0); + continue; + } + + if (!is_const) { + auto path_value = path_column->get_data_at(i); + path.clean(); + if (!path.seek(path_value.data, path_value.size)) { + return Status::InvalidArgument( + "Json path error: {} for value: {}", + JsonbErrMsg::getErrMsg(JsonbErrType::E_INVALID_JSON_PATH), + std::string_view(reinterpret_cast<const char*>(path_value.data), + path_value.size)); + } + } + + auto jsonb_value1 = jsonb_data1_column->get_data_at(i); + auto jsonb_value2 = jsonb_data2_column->get_data_at(i); + + // doc is NOT necessary to be deleted since JsonbDocument will not allocate memory + JsonbDocument* doc1 = + JsonbDocument::createDocument(jsonb_value1.data, jsonb_value1.size); + JsonbDocument* doc2 = + JsonbDocument::createDocument(jsonb_value2.data, jsonb_value2.size); + + JsonbValue* value1 = doc1->getValue()->findValue(path, nullptr); + JsonbValue* value2 = doc2->getValue(); + if (UNLIKELY(jsonb_value1.size == 0 || jsonb_value2.size == 0 || !value1 || !value2)) { + null_map->get_data()[i] = 1; + res->insert_data(nullptr, 0); + continue; + } + auto contains_value = value1->contains(value2); + res->insert_data(const_cast<const char*>((char*)&contains_value), 0); + } + + block.replace_by_position(result, + ColumnNullable::create(std::move(res), std::move(null_map))); + return Status::OK(); + } +}; + +struct JsonbContainsImpl { + static DataTypes get_variadic_argument_types() { + return {std::make_shared<DataTypeJsonb>(), std::make_shared<DataTypeJsonb>()}; + } + + static Status execute_impl(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + auto path = ColumnString::create(); + std::string root_path = "$"; + + for (int i = 0; i < input_rows_count; i++) { + reinterpret_cast<ColumnString*>(path.get()) + ->insert_data(root_path.data(), root_path.size()); + } + + block.insert({std::move(path), std::make_shared<DataTypeString>(), "path"}); + ColumnNumbers temp_arguments = {arguments[0], arguments[1], block.columns() - 1}; + + return JsonbContainsUtil::jsonb_contains_execute(context, block, temp_arguments, result, + input_rows_count); + } +}; + +struct JsonbContainsAndPathImpl { + static DataTypes get_variadic_argument_types() { + return {std::make_shared<DataTypeJsonb>(), std::make_shared<DataTypeJsonb>(), + std::make_shared<DataTypeString>()}; + } + + static Status execute_impl(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + return JsonbContainsUtil::jsonb_contains_execute(context, block, arguments, result, + input_rows_count); + } +}; + void register_function_jsonb(SimpleFunctionFactory& factory) { factory.register_function<FunctionJsonbParse>(FunctionJsonbParse::name); factory.register_alias(FunctionJsonbParse::name, FunctionJsonbParse::alias); @@ -1067,6 +1320,11 @@ void register_function_jsonb(SimpleFunctionFactory& factory) { factory.register_alias(FunctionJsonbExtractString::name, FunctionJsonbExtractString::alias); factory.register_function<FunctionJsonbExtractJsonb>(); // factory.register_alias(FunctionJsonbExtractJsonb::name, FunctionJsonbExtractJsonb::alias); + + factory.register_function<FunctionJsonbLength<JsonbLengthImpl>>(); + factory.register_function<FunctionJsonbLength<JsonbLengthAndPathImpl>>(); + factory.register_function<FunctionJsonbContains<JsonbContainsImpl>>(); + factory.register_function<FunctionJsonbContains<JsonbContainsAndPathImpl>>(); } } // namespace doris::vectorized diff --git a/docs/en/docs/sql-manual/sql-functions/json-functions/json-contains.md b/docs/en/docs/sql-manual/sql-functions/json-functions/json-contains.md new file mode 100644 index 0000000000..78491b6462 --- /dev/null +++ b/docs/en/docs/sql-manual/sql-functions/json-functions/json-contains.md @@ -0,0 +1,79 @@ +--- +{ +"title": "JSON_CONTAINS", +"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_contains +### description +#### Syntax + +`BOOLEAN json_contains(JSON json_str, JSON candidate)` +`BOOLEAN json_contains(JSON json_str, JSON candidate, VARCHAR json_path)` +`BOOLEAN json_contains(VARCHAR json_str, VARCHAR candidate, VARCHAR json_path)` + + +Indicates by returning 1 or 0 whether a given candidate JSON document is contained at a specific path within the json_str JSON document + +### example + +``` +mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; +mysql> SET @j2 = '1'; +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.a') | ++-------------------------------+ +| 1 | ++-------------------------------+ +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.b') | ++-------------------------------+ +| 0 | ++-------------------------------+ + +mysql> SET @j2 = '{"d": 4}'; +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.a') | ++-------------------------------+ +| 0 | ++-------------------------------+ +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.c') | ++-------------------------------+ +| 1 | ++-------------------------------+ + +mysql> SELECT json_contains('[1, 2, {"x": 3}]', '1'); ++----------------------------------------+ +| json_contains('[1, 2, {"x": 3}]', '1') | ++----------------------------------------+ +| 1 | ++----------------------------------------+ +1 row in set (0.04 sec) +``` +### keywords +json,json_contains diff --git a/docs/en/docs/sql-manual/sql-functions/json-functions/json-length.md b/docs/en/docs/sql-manual/sql-functions/json-functions/json-length.md new file mode 100644 index 0000000000..6ea8f25314 --- /dev/null +++ b/docs/en/docs/sql-manual/sql-functions/json-functions/json-length.md @@ -0,0 +1,68 @@ +--- +{ +"title": "JSON_LENGTH", +"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_length +### description +#### Syntax + +`INT json_length(JSON json_str)` +`INT json_length(JSON json_str, VARCHAR json_path)` + +If specified path, the JSON_LENGTH() function returns the length of the data matching the path in the JSON document, otherwise it returns the length of the JSON document. The function calculates the length of the JSON document according to the following rules: + +* The length of a scalar is 1. For example, the length of 1, '"x"', true, false, null is all 1. +* The length of an array is the number of array elements. For example, the length of [1, 2] is 2. +* The length of an object is the number of object members. For example, the length of {"x": 1} is 1. + +### example + +``` +mysql> SELECT json_length('{"k1":"v31","k2":300}'); ++--------------------------------------+ +| json_length('{"k1":"v31","k2":300}') | ++--------------------------------------+ +| 2 | ++--------------------------------------+ +1 row in set (0.26 sec) + +mysql> SELECT json_length('"abc"'); ++----------------------+ +| json_length('"abc"') | ++----------------------+ +| 1 | ++----------------------+ +1 row in set (0.17 sec) + +mysql> SELECT json_length('{"x": 1, "y": [1, 2]}', '$.y'); ++---------------------------------------------+ +| json_length('{"x": 1, "y": [1, 2]}', '$.y') | ++---------------------------------------------+ +| 2 | ++---------------------------------------------+ +1 row in set (0.07 sec) +``` +### keywords +json,json_length diff --git a/docs/sidebars.json b/docs/sidebars.json index 1713bbcbbd..8639eaab51 100644 --- a/docs/sidebars.json +++ b/docs/sidebars.json @@ -612,6 +612,8 @@ "sql-manual/sql-functions/json-functions/json_quote", "sql-manual/sql-functions/json-functions/json_unquote", "sql-manual/sql-functions/json-functions/json_valid", + "sql-manual/sql-functions/json-functions/json-contains", + "sql-manual/sql-functions/json-functions/json-length", "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_string" diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-contains.md b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-contains.md new file mode 100644 index 0000000000..31c5403968 --- /dev/null +++ b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-contains.md @@ -0,0 +1,79 @@ +--- +{ +"title": "JSON_CONTAINS", +"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_contains +### description +#### Syntax + +`BOOLEAN json_contains(JSON json_str, JSON candidate)` +`BOOLEAN json_contains(JSON json_str, JSON candidate, VARCHAR json_path)` +`BOOLEAN json_contains(VARCHAR json_str, VARCHAR candidate, VARCHAR json_path)` + + +通过返回 1 或 0 来指示给定的 candidate JSON 文档是否包含在 json_str JSON json_path 路径下的文档中 + +### example + +``` +mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}'; +mysql> SET @j2 = '1'; +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.a') | ++-------------------------------+ +| 1 | ++-------------------------------+ +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.b'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.b') | ++-------------------------------+ +| 0 | ++-------------------------------+ + +mysql> SET @j2 = '{"d": 4}'; +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.a'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.a') | ++-------------------------------+ +| 0 | ++-------------------------------+ +mysql> SELECT JSON_CONTAINS(@j, @j2, '$.c'); ++-------------------------------+ +| JSON_CONTAINS(@j, @j2, '$.c') | ++-------------------------------+ +| 1 | ++-------------------------------+ + +mysql> SELECT json_contains('[1, 2, {"x": 3}]', '1'); ++----------------------------------------+ +| json_contains('[1, 2, {"x": 3}]', '1') | ++----------------------------------------+ +| 1 | ++----------------------------------------+ +1 row in set (0.04 sec) +``` +### keywords +json,json_contains diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-length.md b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-length.md new file mode 100644 index 0000000000..5ff9f1c188 --- /dev/null +++ b/docs/zh-CN/docs/sql-manual/sql-functions/json-functions/json-length.md @@ -0,0 +1,68 @@ +--- +{ +"title": "JSON_LENGTH", +"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_length +### description +#### Syntax + +`INT json_length(JSON json_str)` +`INT json_length(JSON json_str, VARCHAR json_path)` + +如果指定path，该JSON_LENGTH()函数返回与 JSON 文档中的路径匹配的数据的长度，否则返回 JSON 文档的长度。该函数根据以下规则计算 JSON 文档的长度： + +* 标量的长度为 1。例如: '1', '"x"', 'true', 'false', 'null' 的长度均为 1。 +* 数组的长度是数组元素的数量。例如: '[1, 2]' 的长度为2。 +* 对象的长度是对象成员的数量。例如: '{"x": 1}' 的长度为1 + +### example + +``` +mysql> SELECT json_length('{"k1":"v31","k2":300}'); ++--------------------------------------+ +| json_length('{"k1":"v31","k2":300}') | ++--------------------------------------+ +| 2 | ++--------------------------------------+ +1 row in set (0.26 sec) + +mysql> SELECT json_length('"abc"'); ++----------------------+ +| json_length('"abc"') | ++----------------------+ +| 1 | ++----------------------+ +1 row in set (0.17 sec) + +mysql> SELECT json_length('{"x": 1, "y": [1, 2]}', '$.y'); ++---------------------------------------------+ +| json_length('{"x": 1, "y": [1, 2]}', '$.y') | ++---------------------------------------------+ +| 2 | ++---------------------------------------------+ +1 row in set (0.07 sec) +``` +### keywords +json,json_length 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 78f4026873..b4d5e0e811 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 @@ -165,7 +165,9 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.If; import org.apache.doris.nereids.trees.expressions.functions.scalar.Initcap; 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.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.JsonUnQuote; @@ -532,6 +534,8 @@ public class BuiltinScalarFunctions implements FunctionHelper { scalar(JsonbParseNullableErrorToNull.class, "jsonb_parse_nullable_error_to_null"), scalar(JsonbParseNullableErrorToValue.class, "jsonb_parse_nullable_error_to_value"), scalar(JsonbType.class, "jsonb_type"), + scalar(JsonLength.class, "json_length"), + scalar(JsonContains.class, "json_conatins"), scalar(LastDay.class, "last_day"), scalar(Least.class, "least"), scalar(Left.class, "left"), diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonContains.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonContains.java new file mode 100644 index 0000000000..afae87c0a5 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonContains.java @@ -0,0 +1,86 @@ +// 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.literal.Literal; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BooleanType; +import org.apache.doris.nereids.types.JsonType; +import org.apache.doris.nereids.types.StringType; +import org.apache.doris.nereids.types.VarcharType; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'json_contains'. This class is generated by GenerateFunction . + */ +public class JsonContains extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, AlwaysNullable { + + public static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(BooleanType.INSTANCE).args(JsonType.INSTANCE, JsonType.INSTANCE), + FunctionSignature.ret(BooleanType.INSTANCE).args(JsonType.INSTANCE, JsonType.INSTANCE, + VarcharType.SYSTEM_DEFAULT), + FunctionSignature.ret(BooleanType.INSTANCE).args(JsonType.INSTANCE, JsonType.INSTANCE, StringType.INSTANCE) + ); + + /** + * constructor with 2 arguments. + */ + public JsonContains(Expression arg0, Expression arg1) { + super("json_contains", arg0, arg1, Literal.of("")); + } + + /** + * constructor with 3 arguments. + */ + public JsonContains(Expression arg0, Expression arg1, Expression arg2) { + super("json_contains", arg0, arg1, arg2); + } + + /** + * withChildren. + */ + @Override + public JsonContains withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 2 || children.size() == 3); + if (children.size() == 2) { + return new JsonContains(children.get(0), children.get(1)); + } else { + return new JsonContains(children.get(0), children.get(1), children.get(2)); + } + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitJsonContains(this, context); + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonLength.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonLength.java new file mode 100644 index 0000000000..fbe127d877 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonLength.java @@ -0,0 +1,86 @@ +// 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.literal.Literal; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.IntegerType; +import org.apache.doris.nereids.types.JsonType; +import org.apache.doris.nereids.types.StringType; +import org.apache.doris.nereids.types.VarcharType; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'json_length'. This class is generated by GenerateFunction. + */ +public class JsonLength extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullable { + + public static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(IntegerType.INSTANCE).args(JsonType.INSTANCE), + FunctionSignature.ret(IntegerType.INSTANCE).args(JsonType.INSTANCE, VarcharType.SYSTEM_DEFAULT), + FunctionSignature.ret(IntegerType.INSTANCE).args(JsonType.INSTANCE, StringType.INSTANCE) + ); + + /** + * constructor with 1 arguments. + */ + public JsonLength(Expression arg0) { + super("json_length", arg0, Literal.of("")); + } + + /** + * constructor with 2 arguments. + */ + public JsonLength(Expression arg0, Expression arg1) { + super("json_length", arg0, arg1); + } + + /** + * withChildren. + */ + @Override + public JsonLength withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1 || children.size() == 2); + if (children.size() == 1) { + return new JsonLength(children.get(0)); + } else { + return new JsonLength(children.get(0), children.get(1)); + } + + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitJsonLength(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 5dcc58fea8..efff54db6e 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 @@ -164,7 +164,9 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.If; import org.apache.doris.nereids.trees.expressions.functions.scalar.Initcap; 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.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.JsonUnQuote; @@ -1062,6 +1064,14 @@ public interface ScalarFunctionVisitor<R, C> { return visitScalarFunction(jsonbType, context); } + default R visitJsonLength(JsonLength jsonLength, C context) { + return visitScalarFunction(jsonLength, context); + } + + default R visitJsonContains(JsonContains jsonContains, C context) { + return visitScalarFunction(jsonContains, context); + } + default R visitLastDay(LastDay lastDay, C context) { return visitScalarFunction(lastDay, context); } diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py index 0ee7f342cc..14c4c216b2 100644 --- a/gensrc/script/doris_builtins_functions.py +++ b/gensrc/script/doris_builtins_functions.py @@ -1720,7 +1720,7 @@ visible_functions = { [['json_exists_path'], 'BOOLEAN', ['JSONB', 'VARCHAR'], ''], [['json_exists_path'], 'BOOLEAN', ['JSONB', 'STRING'], ''], - [['json_type'], 'STRING', ['JSONB', 'STRING'], 'ALWAYS_NULLABLE'], + [['json_type'], 'STRING', ['JSONB', 'VARCHAR'], 'ALWAYS_NULLABLE'], [['json_type'], 'STRING', ['JSONB', 'STRING'], 'ALWAYS_NULLABLE'], [['json_extract_isnull'], 'BOOLEAN', ['JSONB', 'VARCHAR'], 'ALWAYS_NULLABLE'], @@ -1737,6 +1737,12 @@ visible_functions = { [['json_extract_double'], 'DOUBLE', ['JSONB', 'STRING'], 'ALWAYS_NULLABLE'], [['json_extract_string'], 'STRING', ['JSONB', 'VARCHAR'], 'ALWAYS_NULLABLE'], [['json_extract_string'], 'STRING', ['JSONB', 'STRING'], 'ALWAYS_NULLABLE'], + [['json_length'], 'INT', ['JSONB'], 'ALWAYS_NULLABLE'], + [['json_length'], 'INT', ['JSONB', 'VARCHAR'], 'ALWAYS_NULLABLE'], + [['json_length'], 'INT', ['JSONB', 'STRING'], 'ALWAYS_NULLABLE'], + [['json_contains'], 'BOOLEAN', ['JSONB', 'JSONB'], 'ALWAYS_NULLABLE'], + [['json_contains'], 'BOOLEAN', ['JSONB', 'JSONB', 'VARCHAR'], 'ALWAYS_NULLABLE'], + [['json_contains'], 'BOOLEAN', ['JSONB', 'JSONB', 'STRING'], 'ALWAYS_NULLABLE'], # Json functions [['get_json_int'], 'INT', ['VARCHAR', 'VARCHAR'], 'ALWAYS_NULLABLE'], @@ -1752,7 +1758,7 @@ visible_functions = { [['json_object'], 'VARCHAR', ['VARCHAR', '...'], 'ALWAYS_NOT_NULLABLE'], [['json_quote'], 'VARCHAR', ['VARCHAR'], ''], [['json_valid'], 'INT', ['VARCHAR'], 'ALWAYS_NULLABLE'], - [['json_contains'], 'INT', ['VARCHAR', 'VARCHAR', 'VARCHAR'], 'ALWAYS_NULLABLE'], + [['json_contains'], 'BOOLEAN', ['VARCHAR', 'VARCHAR', 'VARCHAR'], 'ALWAYS_NULLABLE'], [['json_unquote'], 'VARCHAR', ['VARCHAR'], 'ALWAYS_NULLABLE'], [['json_extract'], 'VARCHAR', ['VARCHAR', 'VARCHAR', '...'], ''] ], diff --git a/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out b/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out index b1731cab21..0e0fabb039 100644 --- a/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out +++ b/regression-test/data/jsonb_p0/test_jsonb_load_and_function.out @@ -7591,3 +7591,265 @@ false 30 -9223372036854775808 null 31 18446744073709551615 null +-- !sql_json_length -- +1 + +-- !sql_json_length -- +1 + +-- !sql_json_length -- +1 + +-- !sql_json_length -- +1 + +-- !sql_json_length -- +0 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !select_length -- +1 \N \N +2 null 1 +3 true 1 +4 false 1 +5 100 1 +6 10000 1 +7 1000000000 1 +8 1152921504606846976 1 +9 6.18 1 +10 "abcd" 1 +11 {} 0 +12 {"k1":"v31","k2":300} 2 +13 [] 0 +14 [123,456] 2 +15 ["abc","def"] 2 +16 [null,true,false,100,6.18,"abc"] 6 +17 [{"k1":"v41","k2":400},1,"a",3.14] 4 +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} 3 +26 \N \N +27 {"k1":"v1","k2":200} 2 +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} 2 +29 12524337771678448270 1 +30 -9223372036854775808 1 +31 18446744073709551615 1 + +-- !select_length -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} \N +13 [] \N +14 [123,456] 1 +15 ["abc","def"] 1 +16 [null,true,false,100,6.18,"abc"] 1 +17 [{"k1":"v41","k2":400},1,"a",3.14] 1 +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N +26 \N \N +27 {"k1":"v1","k2":200} \N +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N +29 12524337771678448270 \N +30 -9223372036854775808 \N +31 18446744073709551615 \N + +-- !select_length -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} 1 +13 [] \N +14 [123,456] \N +15 ["abc","def"] \N +16 [null,true,false,100,6.18,"abc"] \N +17 [{"k1":"v41","k2":400},1,"a",3.14] \N +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} 1 +26 \N \N +27 {"k1":"v1","k2":200} 1 +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N +29 12524337771678448270 \N +30 -9223372036854775808 \N +31 18446744073709551615 \N + +-- !select_length -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} \N +13 [] \N +14 [123,456] \N +15 ["abc","def"] \N +16 [null,true,false,100,6.18,"abc"] \N +17 [{"k1":"v41","k2":400},1,"a",3.14] \N +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N +26 \N \N +27 {"k1":"v1","k2":200} \N +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N +29 12524337771678448270 \N +30 -9223372036854775808 \N +31 18446744073709551615 \N + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +false + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +false + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +true + +-- !select_json_contains -- +1 \N \N +2 null false +3 true true +4 false false +5 100 false +6 10000 false +7 1000000000 false +8 1152921504606846976 false +9 6.18 false +10 "abcd" false +11 {} false +12 {"k1":"v31","k2":300} false +13 [] false +14 [123,456] false +15 ["abc","def"] false +16 [null,true,false,100,6.18,"abc"] true +17 [{"k1":"v41","k2":400},1,"a",3.14] false +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} false +26 \N \N +27 {"k1":"v1","k2":200} false +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} false +29 12524337771678448270 false +30 -9223372036854775808 false +31 18446744073709551615 false + +-- !select_json_contains -- +1 \N \N +2 null false +3 true false +4 false false +5 100 false +6 10000 false +7 1000000000 false +8 1152921504606846976 false +9 6.18 false +10 "abcd" false +11 {} true +12 {"k1":"v31","k2":300} true +13 [] false +14 [123,456] false +15 ["abc","def"] false +16 [null,true,false,100,6.18,"abc"] false +17 [{"k1":"v41","k2":400},1,"a",3.14] false +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} true +26 \N \N +27 {"k1":"v1","k2":200} false +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} true +29 12524337771678448270 false +30 -9223372036854775808 false +31 18446744073709551615 false + +-- !select_json_contains -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} \N +13 [] \N +14 [123,456] \N +15 ["abc","def"] \N +16 [null,true,false,100,6.18,"abc"] \N +17 [{"k1":"v41","k2":400},1,"a",3.14] \N +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} true +26 \N \N +27 {"k1":"v1","k2":200} \N +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N +29 12524337771678448270 \N +30 -9223372036854775808 \N +31 18446744073709551615 \N + +-- !select_json_contains -- +1 \N \N +2 null false +3 true false +4 false false +5 100 false +6 10000 false +7 1000000000 false +8 1152921504606846976 false +9 6.18 false +10 "abcd" false +11 {} false +12 {"k1":"v31","k2":300} false +13 [] false +14 [123,456] true +15 ["abc","def"] false +16 [null,true,false,100,6.18,"abc"] false +17 [{"k1":"v41","k2":400},1,"a",3.14] false +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} false +26 \N \N +27 {"k1":"v1","k2":200} false +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} false +29 12524337771678448270 false +30 -9223372036854775808 false +31 18446744073709551615 false + diff --git a/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out b/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out index 632badf32e..eca0a75867 100644 --- a/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out +++ b/regression-test/data/jsonb_p0/test_jsonb_load_unique_key_and_function.out @@ -5867,3 +5867,241 @@ false -- !select -- \N +-- !sql_json_length -- +1 + +-- !sql_json_length -- +1 + +-- !sql_json_length -- +1 + +-- !sql_json_length -- +1 + +-- !sql_json_length -- +0 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !sql_json_length -- +2 + +-- !select_length -- +1 \N \N +2 null 1 +3 true 1 +4 false 1 +5 100 1 +6 10000 1 +7 1000000000 1 +8 1152921504606846976 1 +9 6.18 1 +10 "abcd" 1 +11 {} 0 +12 {"k1":"v31","k2":300} 2 +13 [] 0 +14 [123,456] 2 +15 ["abc","def"] 2 +16 [null,true,false,100,6.18,"abc"] 6 +17 [{"k1":"v41","k2":400},1,"a",3.14] 4 +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} 3 +26 \N \N +27 {"k1":"v1","k2":200} 2 +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} 2 + +-- !select_length -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} \N +13 [] \N +14 [123,456] 1 +15 ["abc","def"] 1 +16 [null,true,false,100,6.18,"abc"] 1 +17 [{"k1":"v41","k2":400},1,"a",3.14] 1 +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N +26 \N \N +27 {"k1":"v1","k2":200} \N +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N + +-- !select_length -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} 1 +13 [] \N +14 [123,456] \N +15 ["abc","def"] \N +16 [null,true,false,100,6.18,"abc"] \N +17 [{"k1":"v41","k2":400},1,"a",3.14] \N +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} 1 +26 \N \N +27 {"k1":"v1","k2":200} 1 +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N + +-- !select_length -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} \N +13 [] \N +14 [123,456] \N +15 ["abc","def"] \N +16 [null,true,false,100,6.18,"abc"] \N +17 [{"k1":"v41","k2":400},1,"a",3.14] \N +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} \N +26 \N \N +27 {"k1":"v1","k2":200} \N +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +false + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +false + +-- !sql_json_contains -- +true + +-- !sql_json_contains -- +true + +-- !select_json_contains -- +1 \N \N +2 null false +3 true true +4 false false +5 100 false +6 10000 false +7 1000000000 false +8 1152921504606846976 false +9 6.18 false +10 "abcd" false +11 {} false +12 {"k1":"v31","k2":300} false +13 [] false +14 [123,456] false +15 ["abc","def"] false +16 [null,true,false,100,6.18,"abc"] true +17 [{"k1":"v41","k2":400},1,"a",3.14] false +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} false +26 \N \N +27 {"k1":"v1","k2":200} false +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} false + +-- !select_json_contains -- +1 \N \N +2 null false +3 true false +4 false false +5 100 false +6 10000 false +7 1000000000 false +8 1152921504606846976 false +9 6.18 false +10 "abcd" false +11 {} true +12 {"k1":"v31","k2":300} true +13 [] false +14 [123,456] false +15 ["abc","def"] false +16 [null,true,false,100,6.18,"abc"] false +17 [{"k1":"v41","k2":400},1,"a",3.14] false +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} true +26 \N \N +27 {"k1":"v1","k2":200} false +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} true + +-- !select_json_contains -- +1 \N \N +2 null \N +3 true \N +4 false \N +5 100 \N +6 10000 \N +7 1000000000 \N +8 1152921504606846976 \N +9 6.18 \N +10 "abcd" \N +11 {} \N +12 {"k1":"v31","k2":300} \N +13 [] \N +14 [123,456] \N +15 ["abc","def"] \N +16 [null,true,false,100,6.18,"abc"] \N +17 [{"k1":"v41","k2":400},1,"a",3.14] \N +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} true +26 \N \N +27 {"k1":"v1","k2":200} \N +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} \N + +-- !select_json_contains -- +1 \N \N +2 null false +3 true false +4 false false +5 100 false +6 10000 false +7 1000000000 false +8 1152921504606846976 false +9 6.18 false +10 "abcd" false +11 {} false +12 {"k1":"v31","k2":300} false +13 [] false +14 [123,456] true +15 ["abc","def"] false +16 [null,true,false,100,6.18,"abc"] false +17 [{"k1":"v41","k2":400},1,"a",3.14] false +18 {"k1":"v31","k2":300,"a1":[{"k1":"v41","k2":400},1,"a",3.14]} false +26 \N \N +27 {"k1":"v1","k2":200} false +28 {"a.b.c":{"k1.a1":"v31","k2":300},"a":"niu"} false + diff --git a/regression-test/data/query_p0/sql_functions/json_functions/test_json_function.out b/regression-test/data/query_p0/sql_functions/json_functions/test_json_function.out index e776281b5d..764c02da6f 100644 --- a/regression-test/data/query_p0/sql_functions/json_functions/test_json_function.out +++ b/regression-test/data/query_p0/sql_functions/json_functions/test_json_function.out @@ -147,23 +147,23 @@ doris 123 -- !sql -- -1 +true -- !sql -- -0 +false -- !sql -- -0 +false -- !sql -- -1 +true -- !sql -- -0 +false -- !sql -- -1 +true -- !sql -- -1 +true diff --git a/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy b/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy index c885528dbd..821b349d40 100644 --- a/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy +++ b/regression-test/suites/jsonb_p0/test_jsonb_load_and_function.groovy @@ -22,6 +22,8 @@ suite("test_jsonb_load_and_function", "p0") { def testTable = "tbl_test_jsonb" def dataFile = "test_jsonb.csv" + sql """ set experimental_enable_nereids_planner = false """ + sql "DROP TABLE IF EXISTS ${testTable}" sql """ @@ -536,4 +538,36 @@ suite("test_jsonb_load_and_function", "p0") { qt_select """SELECT id, j, j->null FROM ${testTable} ORDER BY id""" qt_select """SELECT id, j, j->'\$.a1[0].k2' FROM ${testTable} ORDER BY id""" qt_select """SELECT id, j, j->'\$.a1[0]'->'\$.k1' FROM ${testTable} ORDER BY id""" + + //json_length + qt_sql_json_length """SELECT json_length('1')""" + qt_sql_json_length """SELECT json_length('true')""" + qt_sql_json_length """SELECT json_length('null')""" + qt_sql_json_length """SELECT json_length('"abc"')""" + qt_sql_json_length """SELECT json_length('[]')""" + qt_sql_json_length """SELECT json_length('[1, 2]')""" + qt_sql_json_length """SELECT json_length('[1, {"x": 2}]')""" + qt_sql_json_length """SELECT json_length('{"x": 1, "y": [1, 2]}', '\$.y')""" + qt_sql_json_length """SELECT json_length('{"k1":"v31","k2":300}')""" + qt_sql_json_length """SELECT json_length('{"a.b.c":{"k1.a1":"v31", "k2": 300},"a":"niu"}')""" + qt_sql_json_length """SELECT json_length('{"a":{"k1.a1":"v31", "k2": 300},"b":"niu"}','\$.a')""" + + qt_select_length """SELECT id, j, json_length(j) FROM ${testTable} ORDER BY id""" + qt_select_length """SELECT id, j, json_length(j, '\$[1]') FROM ${testTable} ORDER BY id""" + qt_select_length """SELECT id, j, json_length(j, '\$.k2') FROM ${testTable} ORDER BY id""" + qt_select_length """SELECT id, j, json_length(null) FROM ${testTable} ORDER BY id""" + + //json_contains + qt_sql_json_contains """SELECT json_contains('[1, 2, {"x": 3}]', '1')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, {"x": 3}]', '{"x": 3}')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, {"x": 3}]', '3')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, [3, 4]]', '2')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, [3, 4]]', '2', '\$[2]')""" + qt_sql_json_contains """SELECT json_contains('{"k1":"v31","k2":300}', '{"k2":300}')""" + qt_sql_json_contains """SELECT json_contains('{"k1":"v31","k2":300}', '{"k2":300,"k1":"v31"}')""" + + qt_select_json_contains """SELECT id, j, json_contains(j, cast('true' as json)) FROM ${testTable} ORDER BY id""" + qt_select_json_contains """SELECT id, j, json_contains(j, cast('{"k2":300}' as json)) FROM ${testTable} ORDER BY id""" + qt_select_json_contains """SELECT id, j, json_contains(j, cast('{"k1":"v41","k2":400}' as json), '\$.a1') FROM ${testTable} ORDER BY id""" + qt_select_json_contains """SELECT id, j, json_contains(j, cast('[123,456]' as json)) FROM ${testTable} ORDER BY id""" } diff --git a/regression-test/suites/jsonb_p0/test_jsonb_load_unique_key_and_function.groovy b/regression-test/suites/jsonb_p0/test_jsonb_load_unique_key_and_function.groovy index 5423ad33fc..3f53721d4b 100644 --- a/regression-test/suites/jsonb_p0/test_jsonb_load_unique_key_and_function.groovy +++ b/regression-test/suites/jsonb_p0/test_jsonb_load_unique_key_and_function.groovy @@ -20,6 +20,8 @@ suite("test_jsonb_unique_load_and_function", "p0") { def testTable = "tbl_test_jsonb_unique" def dataFile = "test_jsonb_unique_key.csv" + sql """ set experimental_enable_nereids_planner = false """ + sql "DROP TABLE IF EXISTS ${testTable}" sql """ @@ -475,4 +477,35 @@ suite("test_jsonb_unique_load_and_function", "p0") { qt_select """SELECT CAST('{x' AS JSONB)""" qt_select """SELECT CAST('[123, abc]' AS JSONB)""" + //json_length + qt_sql_json_length """SELECT json_length('1')""" + qt_sql_json_length """SELECT json_length('true')""" + qt_sql_json_length """SELECT json_length('null')""" + qt_sql_json_length """SELECT json_length('"abc"')""" + qt_sql_json_length """SELECT json_length('[]')""" + qt_sql_json_length """SELECT json_length('[1, 2]')""" + qt_sql_json_length """SELECT json_length('[1, {"x": 2}]')""" + qt_sql_json_length """SELECT json_length('{"x": 1, "y": [1, 2]}', '\$.y')""" + qt_sql_json_length """SELECT json_length('{"k1":"v31","k2":300}')""" + qt_sql_json_length """SELECT json_length('{"a.b.c":{"k1.a1":"v31", "k2": 300},"a":"niu"}')""" + qt_sql_json_length """SELECT json_length('{"a":{"k1.a1":"v31", "k2": 300},"b":"niu"}','\$.a')""" + + qt_select_length """SELECT id, j, json_length(j) FROM ${testTable} ORDER BY id""" + qt_select_length """SELECT id, j, json_length(j, '\$[1]') FROM ${testTable} ORDER BY id""" + qt_select_length """SELECT id, j, json_length(j, '\$.k2') FROM ${testTable} ORDER BY id""" + qt_select_length """SELECT id, j, json_length(null) FROM ${testTable} ORDER BY id""" + + //json_contains + qt_sql_json_contains """SELECT json_contains('[1, 2, {"x": 3}]', '1')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, {"x": 3}]', '{"x": 3}')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, {"x": 3}]', '3')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, [3, 4]]', '2')""" + qt_sql_json_contains """SELECT json_contains('[1, 2, [3, 4]]', '2', '\$[2]')""" + qt_sql_json_contains """SELECT json_contains('{"k1":"v31","k2":300}', '{"k2":300}')""" + qt_sql_json_contains """SELECT json_contains('{"k1":"v31","k2":300}', '{"k2":300,"k1":"v31"}')""" + + qt_select_json_contains """SELECT id, j, json_contains(j, cast('true' as json)) FROM ${testTable} ORDER BY id""" + qt_select_json_contains """SELECT id, j, json_contains(j, cast('{"k2":300}' as json)) FROM ${testTable} ORDER BY id""" + qt_select_json_contains """SELECT id, j, json_contains(j, cast('{"k1":"v41","k2":400}' as json), '\$.a1') FROM ${testTable} ORDER BY id""" + qt_select_json_contains """SELECT id, j, json_contains(j, cast('[123,456]' as json)) FROM ${testTable} ORDER BY id""" } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
