This is an automated email from the ASF dual-hosted git repository.
liulijia pushed a commit to branch branch-2.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.1 by this push:
new 4ac07fe918f [Feature](json) Support json_search function in 2.1
(#41590)
4ac07fe918f is described below
commit 4ac07fe918f59485d786b6ddc5ef9d72e4afd348
Author: Lijia Liu <[email protected]>
AuthorDate: Fri Oct 11 16:33:07 2024 +0800
[Feature](json) Support json_search function in 2.1 (#41590)
cherry-pick #40948
Like mysql, json_search returns the path which point to a json string
witch match the pattern.
`SELECT JSON_SEARCH('["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}]', 'one',
'A_') as res;`
```
+----------+
| res |
+----------+
| "$[2].C" |
+----------+
```
Co-authored-by: liutang123 <[email protected]>
---
be/src/util/jsonb_document.h | 29 ++
be/src/vec/functions/function_jsonb.cpp | 354 +++++++++++++++++++++
be/src/vec/functions/like.cpp | 204 ++++++------
be/src/vec/functions/like.h | 4 +
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../expressions/functions/scalar/JsonSearch.java | 62 ++++
.../expressions/visitor/ScalarFunctionVisitor.java | 5 +
gensrc/script/doris_builtins_functions.py | 2 +
gensrc/script/gen_builtins_functions.py | 11 +-
.../sql_functions/json_functions/json_search.out | 139 ++++++++
.../json_functions/json_search.groovy | 121 +++++++
11 files changed, 827 insertions(+), 106 deletions(-)
diff --git a/be/src/util/jsonb_document.h b/be/src/util/jsonb_document.h
index 8a95ccef8d9..2a9cf8a8191 100644
--- a/be/src/util/jsonb_document.h
+++ b/be/src/util/jsonb_document.h
@@ -345,6 +345,22 @@ struct leg_info {
///type: 0 is member 1 is array
unsigned int type;
+
+ bool to_string(std::string* str) const {
+ if (type == MEMBER_CODE) {
+ str->push_back(BEGIN_MEMBER);
+ str->append(leg_ptr, leg_len);
+ return true;
+ } else if (type == ARRAY_CODE) {
+ str->push_back(BEGIN_ARRAY);
+ std::string int_str = std::to_string(array_index);
+ str->append(int_str);
+ str->push_back(END_ARRAY);
+ return true;
+ } else {
+ return false;
+ }
+ }
};
class JsonbPath {
@@ -362,6 +378,19 @@ public:
leg_vector.emplace_back(leg.release());
}
+ void pop_leg_from_leg_vector() { leg_vector.pop_back(); }
+
+ bool to_string(std::string* res) const {
+ res->push_back(SCOPE);
+ for (const auto& leg : leg_vector) {
+ auto valid = leg->to_string(res);
+ if (!valid) {
+ return false;
+ }
+ }
+ return true;
+ }
+
size_t get_leg_vector_size() { return leg_vector.size(); }
leg_info* get_leg_from_leg_vector(size_t i) { return leg_vector[i].get(); }
diff --git a/be/src/vec/functions/function_jsonb.cpp
b/be/src/vec/functions/function_jsonb.cpp
index 66b875544a3..ee0bad3e992 100644
--- a/be/src/vec/functions/function_jsonb.cpp
+++ b/be/src/vec/functions/function_jsonb.cpp
@@ -61,7 +61,9 @@
#include "vec/data_types/data_type_string.h"
#include "vec/functions/function.h"
#include "vec/functions/function_string.h"
+#include "vec/functions/like.h"
#include "vec/functions/simple_function_factory.h"
+#include "vec/json/simd_json_parser.h"
#include "vec/utils/util.hpp"
namespace doris::vectorized {
@@ -1597,6 +1599,356 @@ struct JsonbContainsAndPathImpl {
}
};
+class FunctionJsonSearch : public IFunction {
+private:
+ using OneFun = std::function<Status(size_t, bool*)>;
+ static Status always_one(size_t i, bool* res) {
+ *res = true;
+ return Status::OK();
+ }
+ static Status always_all(size_t i, bool* res) {
+ *res = false;
+ return Status::OK();
+ }
+
+ using CheckNullFun = std::function<bool(size_t)>;
+ static bool always_not_null(size_t) { return false; }
+ static bool always_null(size_t) { return true; }
+
+ using GetJsonStringRefFun = std::function<StringRef(size_t)>;
+
+ Status matched(const std::string_view& str, LikeState* state, unsigned
char* res) const {
+ StringRef pattern; // not used
+ StringRef value_val(str.data(), str.size());
+ return (state->scalar_function)(&state->search_state, value_val,
pattern, res);
+ }
+
+ /**
+ * Recursive search for matching string, if found, the result will be
added to a vector
+ * @param element json element
+ * @param one_match
+ * @param search_str
+ * @param cur_path
+ * @param matches The path that has already been matched
+ * @return true if matched else false
+ */
+ bool find_matches(const SimdJSONParser::Element& element, const bool&
one_match,
+ LikeState* state, JsonbPath* cur_path,
+ std::unordered_set<std::string>* matches) const {
+ if (element.isString()) {
+ const std::string_view str = element.getString();
+ unsigned char res;
+ RETURN_IF_ERROR(matched(str, state, &res));
+ if (res) {
+ std::string str;
+ auto valid = cur_path->to_string(&str);
+ if (!valid) {
+ return false;
+ }
+ auto res = matches->insert(str);
+ return res.second;
+ } else {
+ return false;
+ }
+ } else if (element.isObject()) {
+ const SimdJSONParser::Object& object = element.getObject();
+ bool find = false;
+ for (size_t i = 0; i < object.size(); ++i) {
+ const SimdJSONParser::KeyValuePair& item = object[i];
+ const std::string_view& key = item.first;
+ const SimdJSONParser::Element& child_element = item.second;
+ // construct an object member path leg.
+ auto leg =
std::make_unique<leg_info>(const_cast<char*>(key.data()), key.size(), 0,
+ MEMBER_CODE);
+ cur_path->add_leg_to_leg_vector(std::move(leg));
+ find |= find_matches(child_element, one_match, state,
cur_path, matches);
+ cur_path->pop_leg_from_leg_vector();
+ if (one_match && find) {
+ return true;
+ }
+ }
+ return find;
+ } else if (element.isArray()) {
+ const SimdJSONParser::Array& array = element.getArray();
+ bool find = false;
+ for (size_t i = 0; i < array.size(); ++i) {
+ auto leg = std::make_unique<leg_info>(nullptr, 0, i,
ARRAY_CODE);
+ cur_path->add_leg_to_leg_vector(std::move(leg));
+ const SimdJSONParser::Element& child_element = array[i];
+ // construct an array cell path leg.
+ find |= find_matches(child_element, one_match, state,
cur_path, matches);
+ cur_path->pop_leg_from_leg_vector();
+ if (one_match && find) {
+ return true;
+ }
+ }
+ return find;
+ } else {
+ return false;
+ }
+ }
+
+ void make_result_str(std::unordered_set<std::string>& matches,
ColumnString* result_col) const {
+ JsonbWriter writer;
+ if (matches.size() == 1) {
+ for (const auto& str_ref : matches) {
+ writer.writeStartString();
+ writer.writeString(str_ref);
+ writer.writeEndString();
+ }
+ } else {
+ writer.writeStartArray();
+ for (const auto& str_ref : matches) {
+ writer.writeStartString();
+ writer.writeString(str_ref);
+ writer.writeEndString();
+ }
+ writer.writeEndArray();
+ }
+
+ result_col->insert_data(writer.getOutput()->getBuffer(),
+ (size_t)writer.getOutput()->getSize());
+ }
+
+ template <bool search_is_const>
+ Status execute_vector(Block& block, size_t input_rows_count, CheckNullFun
json_null_check,
+ GetJsonStringRefFun col_json_string, CheckNullFun
one_null_check,
+ OneFun one_check, CheckNullFun search_null_check,
+ const ColumnString* col_search_string,
FunctionContext* context,
+ size_t result) const {
+ auto result_col = ColumnString::create();
+ auto null_map = ColumnUInt8::create(input_rows_count, 0);
+
+ std::shared_ptr<LikeState> state_ptr;
+ LikeState* state = nullptr;
+ if (search_is_const) {
+ state = reinterpret_cast<LikeState*>(
+
context->get_function_state(FunctionContext::THREAD_LOCAL));
+ }
+
+ SimdJSONParser parser;
+ SimdJSONParser::Element root_element;
+ bool is_one = false;
+
+ for (size_t i = 0; i < input_rows_count; ++i) {
+ // an error occurs if the json_doc argument is not a valid json
document.
+ if (json_null_check(i)) {
+ null_map->get_data()[i] = 1;
+ result_col->insert_data("", 0);
+ continue;
+ }
+ const auto& json_doc = col_json_string(i);
+ if (!parser.parse({json_doc.data, json_doc.size}, root_element)) {
+ return Status::InvalidArgument(
+ "the json_doc argument {} is not a valid json
document", json_doc);
+ }
+
+ if (!one_null_check(i)) {
+ RETURN_IF_ERROR(one_check(i, &is_one));
+ }
+
+ if (one_null_check(i) || search_null_check(i)) {
+ null_map->get_data()[i] = 1;
+ result_col->insert_data("", 0);
+ continue;
+ }
+
+ // an error occurs if any path argument is not a valid path
expression.
+ std::string root_path_str = "$";
+ JsonbPath root_path;
+ root_path.seek(root_path_str.c_str(), root_path_str.size());
+ std::vector<JsonbPath*> paths;
+ paths.push_back(&root_path);
+
+ if (!search_is_const) {
+ state_ptr = std::make_shared<LikeState>();
+ state_ptr->is_like_pattern = true;
+ const auto& search_str = col_search_string->get_data_at(i);
+
RETURN_IF_ERROR(FunctionLike::construct_like_const_state(context, search_str,
+
state_ptr, false));
+ state = state_ptr.get();
+ }
+
+ // maintain a hashset to deduplicate matches.
+ std::unordered_set<std::string> matches;
+ for (const auto& item : paths) {
+ auto cur_path = item;
+ auto find = find_matches(root_element, is_one, state,
cur_path, &matches);
+ if (is_one && find) {
+ break;
+ }
+ }
+ if (matches.empty()) {
+ // returns NULL if the search_str is not found in the document.
+ null_map->get_data()[i] = 1;
+ result_col->insert_data("", 0);
+ continue;
+ }
+ make_result_str(matches, result_col.get());
+ }
+ auto result_col_nullable =
+ ColumnNullable::create(std::move(result_col),
std::move(null_map));
+ block.replace_by_position(result, std::move(result_col_nullable));
+ return Status::OK();
+ }
+
+ static constexpr auto one = "one";
+ static constexpr auto all = "all";
+
+public:
+ static constexpr auto name = "json_search";
+ static FunctionPtr create() { return
std::make_shared<FunctionJsonSearch>(); }
+
+ String get_name() const override { return name; }
+ bool is_variadic() const override { return false; }
+ 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<DataTypeJsonb>());
+ }
+
+ bool use_default_implementation_for_nulls() const override { return false;
}
+
+ Status open(FunctionContext* context, FunctionContext::FunctionStateScope
scope) override {
+ if (scope != FunctionContext::THREAD_LOCAL) {
+ return Status::OK();
+ }
+ if (context->is_col_constant(2)) {
+ std::shared_ptr<LikeState> state = std::make_shared<LikeState>();
+ state->is_like_pattern = true;
+ const auto pattern_col = context->get_constant_col(2)->column_ptr;
+ const auto& pattern = pattern_col->get_data_at(0);
+ RETURN_IF_ERROR(
+ FunctionLike::construct_like_const_state(context, pattern,
state, false));
+ context->set_function_state(scope, state);
+ }
+ return Status::OK();
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const
ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) const override
{
+ // the json_doc, one_or_all, and search_str must be given.
+ // and we require the positions are static.
+ if (arguments.size() < 3) {
+ return Status::InvalidArgument("too few arguments for function
{}", name);
+ }
+ if (arguments.size() > 3) {
+ return Status::NotSupported("escape and path params are not
support now");
+ }
+
+ CheckNullFun json_null_check = always_not_null;
+ GetJsonStringRefFun get_json_fun;
+ ColumnPtr col_json;
+ bool json_is_const = false;
+ // prepare jsonb data column
+ std::tie(col_json, json_is_const) =
+ unpack_if_const(block.get_by_position(arguments[0]).column);
+ const ColumnString* col_json_string =
check_and_get_column<ColumnString>(col_json);
+ if (auto* nullable = check_and_get_column<ColumnNullable>(col_json)) {
+ col_json_string =
check_and_get_column<ColumnString>(nullable->get_nested_column_ptr());
+ }
+
+ if (!col_json_string) {
+ return Status::RuntimeError("Illegal arg json {} should be
ColumnString",
+ col_json->get_name());
+ }
+ if (json_is_const) {
+ if (col_json->is_null_at(0)) {
+ json_null_check = always_null;
+ } else {
+ const auto& json_str = col_json_string->get_data_at(0);
+ get_json_fun = [json_str](size_t i) { return json_str; };
+ }
+ } else {
+ json_null_check = [col_json](size_t i) { return
col_json->is_null_at(i); };
+ get_json_fun = [col_json_string](size_t i) { return
col_json_string->get_data_at(i); };
+ }
+
+ // one_or_all
+ CheckNullFun one_null_check = always_not_null;
+ OneFun one_check = always_one;
+ ColumnPtr col_one;
+ bool one_is_const = false;
+ // prepare jsonb data column
+ std::tie(col_one, one_is_const) =
+ unpack_if_const(block.get_by_position(arguments[1]).column);
+ const ColumnString* col_one_string =
check_and_get_column<ColumnString>(col_one);
+ if (auto* nullable = check_and_get_column<ColumnNullable>(col_one)) {
+ col_one_string =
check_and_get_column<ColumnString>(*nullable->get_nested_column_ptr());
+ }
+ if (!col_one_string) {
+ return Status::RuntimeError("Illegal arg one {} should be
ColumnString",
+ col_one->get_name());
+ }
+ if (one_is_const) {
+ if (col_one->is_null_at(0)) {
+ one_null_check = always_null;
+ } else {
+ const auto& one_or_all = col_one_string->get_data_at(0);
+ std::string one_or_all_str = one_or_all.to_string();
+ if (strcasecmp(one_or_all_str.c_str(), all) == 0) {
+ one_check = always_all;
+ } else if (strcasecmp(one_or_all_str.c_str(), one) == 0) {
+ // nothing
+ } else {
+ // an error occurs if the one_or_all argument is not 'one'
nor 'all'.
+ return Status::InvalidArgument(
+ "the one_or_all argument {} is not 'one' not
'all'", one_or_all_str);
+ }
+ }
+ } else {
+ one_null_check = [col_one](size_t i) { return
col_one->is_null_at(i); };
+ one_check = [col_one_string](size_t i, bool* is_one) {
+ const auto& one_or_all = col_one_string->get_data_at(i);
+ std::string one_or_all_str = one_or_all.to_string();
+ if (strcasecmp(one_or_all_str.c_str(), all) == 0) {
+ *is_one = false;
+ } else if (strcasecmp(one_or_all_str.c_str(), one) == 0) {
+ *is_one = true;
+ } else {
+ // an error occurs if the one_or_all argument is not 'one'
nor 'all'.
+ return Status::InvalidArgument(
+ "the one_or_all argument {} is not 'one' not
'all'", one_or_all_str);
+ }
+ return Status::OK();
+ };
+ }
+
+ // search_str
+ ColumnPtr col_search;
+ bool search_is_const = false;
+ std::tie(col_search, search_is_const) =
+ unpack_if_const(block.get_by_position(arguments[2]).column);
+
+ const ColumnString* col_search_string =
check_and_get_column<ColumnString>(col_search);
+ if (auto* nullable = check_and_get_column<ColumnNullable>(col_search))
{
+ col_search_string =
+
check_and_get_column<ColumnString>(*nullable->get_nested_column_ptr());
+ }
+ if (!col_search_string) {
+ return Status::RuntimeError("Illegal arg pattern {} should be
ColumnString",
+ col_search->get_name());
+ }
+ if (search_is_const) {
+ CheckNullFun search_null_check = always_not_null;
+ if (col_search->is_null_at(0)) {
+ search_null_check = always_null;
+ }
+ RETURN_IF_ERROR(execute_vector<true>(
+ block, input_rows_count, json_null_check, get_json_fun,
one_null_check,
+ one_check, search_null_check, col_search_string, context,
result));
+ } else {
+ CheckNullFun search_null_check = [col_search](size_t i) {
+ return col_search->is_null_at(i);
+ };
+ RETURN_IF_ERROR(execute_vector<false>(
+ block, input_rows_count, json_null_check, get_json_fun,
one_null_check,
+ one_check, search_null_check, col_search_string, context,
result));
+ }
+ return Status::OK();
+ }
+};
+
void register_function_jsonb(SimpleFunctionFactory& factory) {
factory.register_function<FunctionJsonbParse>(FunctionJsonbParse::name);
factory.register_alias(FunctionJsonbParse::name,
FunctionJsonbParse::alias);
@@ -1665,6 +2017,8 @@ void register_function_jsonb(SimpleFunctionFactory&
factory) {
factory.register_function<FunctionJsonbLength<JsonbLengthAndPathImpl>>();
factory.register_function<FunctionJsonbContains<JsonbContainsImpl>>();
factory.register_function<FunctionJsonbContains<JsonbContainsAndPathImpl>>();
+
+ factory.register_function<FunctionJsonSearch>();
}
} // namespace doris::vectorized
diff --git a/be/src/vec/functions/like.cpp b/be/src/vec/functions/like.cpp
index 376e28c0690..0207089b666 100644
--- a/be/src/vec/functions/like.cpp
+++ b/be/src/vec/functions/like.cpp
@@ -816,119 +816,121 @@ void verbose_log_match(const std::string& str, const
std::string& pattern_name,
}
}
+Status FunctionLike::construct_like_const_state(FunctionContext* context,
const StringRef& pattern,
+ std::shared_ptr<LikeState>&
state,
+ bool try_hyperscan) {
+ std::string pattern_str = pattern.to_string();
+ state->search_state.pattern_str = pattern_str;
+ std::string search_string;
+
+ if (!pattern_str.empty() && RE2::FullMatch(pattern_str, LIKE_ALLPASS_RE)) {
+ state->search_state.set_search_string("");
+ state->function = constant_allpass_fn;
+ state->scalar_function = constant_allpass_fn_scalar;
+ } else if (pattern_str.empty() || RE2::FullMatch(pattern_str,
LIKE_EQUALS_RE, &search_string)) {
+ if (VLOG_DEBUG_IS_ON) {
+ verbose_log_match(pattern_str, "LIKE_EQUALS_RE", LIKE_EQUALS_RE);
+ VLOG_DEBUG << "search_string : " << search_string << ", size: " <<
search_string.size();
+ }
+ remove_escape_character(&search_string);
+ if (VLOG_DEBUG_IS_ON) {
+ VLOG_DEBUG << "search_string escape removed: " << search_string
+ << ", size: " << search_string.size();
+ }
+ state->search_state.set_search_string(search_string);
+ state->function = constant_equals_fn;
+ state->scalar_function = constant_equals_fn_scalar;
+ } else if (RE2::FullMatch(pattern_str, LIKE_STARTS_WITH_RE,
&search_string)) {
+ if (VLOG_DEBUG_IS_ON) {
+ verbose_log_match(pattern_str, "LIKE_STARTS_WITH_RE",
LIKE_STARTS_WITH_RE);
+ VLOG_DEBUG << "search_string : " << search_string << ", size: " <<
search_string.size();
+ }
+ remove_escape_character(&search_string);
+ if (VLOG_DEBUG_IS_ON) {
+ VLOG_DEBUG << "search_string escape removed: " << search_string
+ << ", size: " << search_string.size();
+ }
+ state->search_state.set_search_string(search_string);
+ state->function = constant_starts_with_fn;
+ state->scalar_function = constant_starts_with_fn_scalar;
+ } else if (RE2::FullMatch(pattern_str, LIKE_ENDS_WITH_RE, &search_string))
{
+ if (VLOG_DEBUG_IS_ON) {
+ verbose_log_match(pattern_str, "LIKE_ENDS_WITH_RE",
LIKE_ENDS_WITH_RE);
+ VLOG_DEBUG << "search_string : " << search_string << ", size: " <<
search_string.size();
+ }
+ remove_escape_character(&search_string);
+ if (VLOG_DEBUG_IS_ON) {
+ VLOG_DEBUG << "search_string escape removed: " << search_string
+ << ", size: " << search_string.size();
+ }
+ state->search_state.set_search_string(search_string);
+ state->function = constant_ends_with_fn;
+ state->scalar_function = constant_ends_with_fn_scalar;
+ } else if (RE2::FullMatch(pattern_str, LIKE_SUBSTRING_RE, &search_string))
{
+ if (VLOG_DEBUG_IS_ON) {
+ verbose_log_match(pattern_str, "LIKE_SUBSTRING_RE",
LIKE_SUBSTRING_RE);
+ VLOG_DEBUG << "search_string : " << search_string << ", size: " <<
search_string.size();
+ }
+ remove_escape_character(&search_string);
+ if (VLOG_DEBUG_IS_ON) {
+ VLOG_DEBUG << "search_string escape removed: " << search_string
+ << ", size: " << search_string.size();
+ }
+ state->search_state.set_search_string(search_string);
+ state->function = constant_substring_fn;
+ state->scalar_function = constant_substring_fn_scalar;
+ } else {
+ std::string re_pattern;
+ convert_like_pattern(&state->search_state, pattern_str, &re_pattern);
+ if (VLOG_DEBUG_IS_ON) {
+ VLOG_DEBUG << "hyperscan, pattern str: " << pattern_str
+ << ", size: " << pattern_str.size() << ", re pattern: "
<< re_pattern
+ << ", size: " << re_pattern.size();
+ }
+
+ hs_database_t* database = nullptr;
+ hs_scratch_t* scratch = nullptr;
+ if (try_hyperscan && hs_prepare(context, re_pattern.c_str(),
&database, &scratch).ok()) {
+ // use hyperscan
+ state->search_state.hs_database.reset(database);
+ state->search_state.hs_scratch.reset(scratch);
+ } else {
+ // fallback to re2
+ // reset hs_database to nullptr to indicate not use hyperscan
+ state->search_state.hs_database.reset();
+ state->search_state.hs_scratch.reset();
+
+ RE2::Options opts;
+ opts.set_never_nl(false);
+ opts.set_dot_nl(true);
+ state->search_state.regex = std::make_unique<RE2>(re_pattern,
opts);
+ if (!state->search_state.regex->ok()) {
+ return Status::InternalError("Invalid regex expression:
{}(origin: {})", re_pattern,
+ pattern_str);
+ }
+ }
+
+ state->function = constant_regex_fn;
+ state->scalar_function = constant_regex_fn_scalar;
+ }
+ return Status::OK();
+}
+
Status FunctionLike::open(FunctionContext* context,
FunctionContext::FunctionStateScope scope) {
if (scope != FunctionContext::THREAD_LOCAL) {
return Status::OK();
}
std::shared_ptr<LikeState> state = std::make_shared<LikeState>();
- context->set_function_state(scope, state);
state->is_like_pattern = true;
state->function = like_fn;
state->scalar_function = like_fn_scalar;
if (context->is_col_constant(1)) {
const auto pattern_col = context->get_constant_col(1)->column_ptr;
const auto& pattern = pattern_col->get_data_at(0);
-
- std::string pattern_str = pattern.to_string();
- state->search_state.pattern_str = pattern_str;
- std::string search_string;
-
- if (!pattern_str.empty() && RE2::FullMatch(pattern_str,
LIKE_ALLPASS_RE)) {
- state->search_state.set_search_string("");
- state->function = constant_allpass_fn;
- state->scalar_function = constant_allpass_fn_scalar;
- } else if (pattern_str.empty() ||
- RE2::FullMatch(pattern_str, LIKE_EQUALS_RE,
&search_string)) {
- if (VLOG_DEBUG_IS_ON) {
- verbose_log_match(pattern_str, "LIKE_EQUALS_RE",
LIKE_EQUALS_RE);
- VLOG_DEBUG << "search_string : " << search_string
- << ", size: " << search_string.size();
- }
- remove_escape_character(&search_string);
- if (VLOG_DEBUG_IS_ON) {
- VLOG_DEBUG << "search_string escape removed: " << search_string
- << ", size: " << search_string.size();
- }
- state->search_state.set_search_string(search_string);
- state->function = constant_equals_fn;
- state->scalar_function = constant_equals_fn_scalar;
- } else if (RE2::FullMatch(pattern_str, LIKE_STARTS_WITH_RE,
&search_string)) {
- if (VLOG_DEBUG_IS_ON) {
- verbose_log_match(pattern_str, "LIKE_STARTS_WITH_RE",
LIKE_STARTS_WITH_RE);
- VLOG_DEBUG << "search_string : " << search_string
- << ", size: " << search_string.size();
- }
- remove_escape_character(&search_string);
- if (VLOG_DEBUG_IS_ON) {
- VLOG_DEBUG << "search_string escape removed: " << search_string
- << ", size: " << search_string.size();
- }
- state->search_state.set_search_string(search_string);
- state->function = constant_starts_with_fn;
- state->scalar_function = constant_starts_with_fn_scalar;
- } else if (RE2::FullMatch(pattern_str, LIKE_ENDS_WITH_RE,
&search_string)) {
- if (VLOG_DEBUG_IS_ON) {
- verbose_log_match(pattern_str, "LIKE_ENDS_WITH_RE",
LIKE_ENDS_WITH_RE);
- VLOG_DEBUG << "search_string : " << search_string
- << ", size: " << search_string.size();
- }
- remove_escape_character(&search_string);
- if (VLOG_DEBUG_IS_ON) {
- VLOG_DEBUG << "search_string escape removed: " << search_string
- << ", size: " << search_string.size();
- }
- state->search_state.set_search_string(search_string);
- state->function = constant_ends_with_fn;
- state->scalar_function = constant_ends_with_fn_scalar;
- } else if (RE2::FullMatch(pattern_str, LIKE_SUBSTRING_RE,
&search_string)) {
- if (VLOG_DEBUG_IS_ON) {
- verbose_log_match(pattern_str, "LIKE_SUBSTRING_RE",
LIKE_SUBSTRING_RE);
- VLOG_DEBUG << "search_string : " << search_string
- << ", size: " << search_string.size();
- }
- remove_escape_character(&search_string);
- if (VLOG_DEBUG_IS_ON) {
- VLOG_DEBUG << "search_string escape removed: " << search_string
- << ", size: " << search_string.size();
- }
- state->search_state.set_search_string(search_string);
- state->function = constant_substring_fn;
- state->scalar_function = constant_substring_fn_scalar;
- } else {
- std::string re_pattern;
- convert_like_pattern(&state->search_state, pattern_str,
&re_pattern);
- if (VLOG_DEBUG_IS_ON) {
- VLOG_DEBUG << "hyperscan, pattern str: " << pattern_str
- << ", size: " << pattern_str.size() << ", re
pattern: " << re_pattern
- << ", size: " << re_pattern.size();
- }
-
- hs_database_t* database = nullptr;
- hs_scratch_t* scratch = nullptr;
- if (hs_prepare(context, re_pattern.c_str(), &database,
&scratch).ok()) {
- // use hyperscan
- state->search_state.hs_database.reset(database);
- state->search_state.hs_scratch.reset(scratch);
- } else {
- // fallback to re2
- // reset hs_database to nullptr to indicate not use hyperscan
- state->search_state.hs_database.reset();
- state->search_state.hs_scratch.reset();
-
- RE2::Options opts;
- opts.set_never_nl(false);
- opts.set_dot_nl(true);
- state->search_state.regex = std::make_unique<RE2>(re_pattern,
opts);
- if (!state->search_state.regex->ok()) {
- return Status::InternalError("Invalid regex expression:
{}(origin: {})",
- re_pattern, pattern_str);
- }
- }
-
- state->function = constant_regex_fn;
- state->scalar_function = constant_regex_fn_scalar;
- }
+ RETURN_IF_ERROR(construct_like_const_state(context, pattern, state));
}
+ context->set_function_state(scope, state);
+
return Status::OK();
}
diff --git a/be/src/vec/functions/like.h b/be/src/vec/functions/like.h
index 1e9cb2e4fad..d56c4c35389 100644
--- a/be/src/vec/functions/like.h
+++ b/be/src/vec/functions/like.h
@@ -256,6 +256,10 @@ public:
Status open(FunctionContext* context, FunctionContext::FunctionStateScope
scope) override;
+ static Status construct_like_const_state(FunctionContext* ctx, const
StringRef& pattern,
+ std::shared_ptr<LikeState>& state,
+ bool try_hyperscan = true);
+
friend struct LikeSearchState;
friend struct VectorAllpassSearchState;
friend struct VectorEqualSearchState;
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 aec39e9df20..4b87fa37652 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
@@ -227,6 +227,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonLength;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonObject;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonQuote;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSearch;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSet;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonUnQuote;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonbExistsPath;
@@ -708,6 +709,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(JsonbParseNullableErrorToNull.class,
"jsonb_parse_nullable_error_to_null"),
scalar(JsonbParseNullableErrorToValue.class,
"json_parse_nullable_error_to_value"),
scalar(JsonbParseNullableErrorToValue.class,
"jsonb_parse_nullable_error_to_value"),
+ scalar(JsonSearch.class, "json_search"),
scalar(JsonbValid.class, "json_valid"),
scalar(JsonbValid.class, "jsonb_valid"),
scalar(JsonbType.class, "json_type"),
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSearch.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSearch.java
new file mode 100644
index 00000000000..6f034308cf7
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/JsonSearch.java
@@ -0,0 +1,62 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.trees.expressions.functions.scalar;
+
+import org.apache.doris.catalog.FunctionSignature;
+import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
+import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.JsonType;
+import org.apache.doris.nereids.types.VarcharType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * JsonSearch returns the json path pointing to a json string witch contains
the search string.
+ */
+public class JsonSearch extends ScalarFunction implements
ExplicitlyCastableSignature, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(JsonType.INSTANCE)
+ .args(VarcharType.SYSTEM_DEFAULT,
VarcharType.SYSTEM_DEFAULT, VarcharType.SYSTEM_DEFAULT)
+ );
+
+ public JsonSearch(Expression arg0, Expression arg1, Expression arg2) {
+ super("json_search", arg0, arg1, arg2);
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public JsonSearch withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 3);
+ return new JsonSearch(children.get(0), children.get(1),
children.get(2));
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitJsonSearch(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 0641a76ac21..a2562baa758 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
@@ -230,6 +230,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonLength;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonObject;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonQuote;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonReplace;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSearch;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonSet;
import org.apache.doris.nereids.trees.expressions.functions.scalar.JsonUnQuote;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.JsonbExistsPath;
@@ -1305,6 +1306,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(jsonKeys, context);
}
+ default R visitJsonSearch(JsonSearch jsonSearch, C context) {
+ return visitScalarFunction(jsonSearch, context);
+ }
+
default R visitJsonInsert(JsonInsert jsonInsert, C context) {
return visitScalarFunction(jsonInsert, context);
}
diff --git a/gensrc/script/doris_builtins_functions.py
b/gensrc/script/doris_builtins_functions.py
index 6deede3784d..4b5e113faee 100644
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -1772,6 +1772,8 @@ visible_functions = {
[['json_parse_notnull_error_to_value'], 'JSONB', ['VARCHAR',
'VARCHAR'], ''],
[['json_parse_notnull_error_to_invalid'], 'JSONB', ['VARCHAR'], ''],
+ [['json_search'], 'JSONB', ['VARCHAR', 'VARCHAR', 'VARCHAR'],
'ALWAYS_NULLABLE'],
+
[['json_exists_path'], 'BOOLEAN', ['JSONB', 'VARCHAR'], ''],
[['json_exists_path'], 'BOOLEAN', ['JSONB', 'STRING'], ''],
[['json_type'], 'STRING', ['JSONB', 'VARCHAR'], 'ALWAYS_NULLABLE'],
diff --git a/gensrc/script/gen_builtins_functions.py
b/gensrc/script/gen_builtins_functions.py
index e50e0d4ede9..619a30d4e15 100755
--- a/gensrc/script/gen_builtins_functions.py
+++ b/gensrc/script/gen_builtins_functions.py
@@ -171,7 +171,7 @@ def generate_fe_registry_init(filename):
for category, functions in
doris_builtins_functions.visible_functions.items():
java_registry_file.write("
init{0}Builtins(functionSet);\n".format(category.capitalize()))
- # add non_null_result_with_null_param_functions
+ # add null_result_with_one_null_param_functions
java_registry_file.write(" Set<String> funcNames =
Sets.newHashSet();\n")
for entry in
doris_builtins_functions.null_result_with_one_null_param_functions:
java_registry_file.write(" funcNames.add(\"%s\");\n" % entry)
@@ -183,10 +183,11 @@ def generate_fe_registry_init(filename):
java_registry_file.write("
nondeterministicFuncNames.add(\"%s\");\n" % entry)
java_registry_file.write("
functionSet.buildNondeterministicFunctions(nondeterministicFuncNames);\n");
- java_registry_file.write(" funcNames = Sets.newHashSet();\n")
- for entry in
doris_builtins_functions.null_result_with_one_null_param_functions:
- java_registry_file.write(" funcNames.add(\"%s\");\n" % entry)
- java_registry_file.write("
functionSet.buildNullResultWithOneNullParamFunction(funcNames);\n");
+ # add null_result_with_one_null_param_functions
+ # java_registry_file.write(" funcNames = Sets.newHashSet();\n")
+ # for entry in
doris_builtins_functions.null_result_with_one_null_param_functions:
+ # java_registry_file.write(" funcNames.add(\"%s\");\n" % entry)
+ # java_registry_file.write("
functionSet.buildNullResultWithOneNullParamFunction(funcNames);\n");
java_registry_file.write(" }\n")
java_registry_file.write("\n")
diff --git
a/regression-test/data/query_p0/sql_functions/json_functions/json_search.out
b/regression-test/data/query_p0/sql_functions/json_functions/json_search.out
new file mode 100644
index 00000000000..d5ecb9cd3b0
--- /dev/null
+++ b/regression-test/data/query_p0/sql_functions/json_functions/json_search.out
@@ -0,0 +1,139 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !one_is_valid_or_null --
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]" "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+7 \N one _% \N \N
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all \N \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all X \N \N
+
+-- !all_const1 --
+"$[2].C"
+
+-- !all_const1 --
+"$[2].C"
+
+-- !all_const2 --
+["$[3].D","$[2].C"]
+
+-- !all_const2 --
+["$[3].D","$[2].C"]
+
+-- !all_const3 --
+"$[0]"
+
+-- !all_const4 --
+"$[0]"
+
+-- !all_const5 --
+"$[0]"
+
+-- !all_const6 --
+"$[2].C"
+
+-- !all_const7 --
+\N
+
+-- !all_const8 --
+\N
+
+-- !one_is_one_const --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]" "$[0]"
+7 \N one _% \N \N
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one \N \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one X \N \N
+
+-- !one_is_all_const --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"] ["$[3].D","$[2].C","$[1][0].B","$[0]"]
+7 \N all _% \N \N
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all \N \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all X \N \N
+
+-- !one_and_pattern_is_const1 --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+7 \N one A \N \N
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one A "$[0]" "$[0]"
+
+-- !one_and_pattern_is_const2 --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+7 \N all A \N \N
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all A "$[0]" "$[0]"
+
+-- !one_and_pattern_is_nullconst --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+7 \N \N \N \N \N
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] \N \N \N \N
+
+-- !json_const1 --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+7 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one _% "$[0]"
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] one X \N
+
+-- !json_const2 --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+7 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all _%
["$[3].D","$[2].C","$[1][0].B","$[0]"]
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] all X \N
+
+-- !one_case1 --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+7 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One _% "$[0]"
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] One X \N
+
+-- !one_case2 --
+1 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+2 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+3 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+4 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+5 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+6 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+7 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All _% "$[0]"
+8 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All \N \N
+9 ["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}] All X \N
+
diff --git
a/regression-test/suites/query_p0/sql_functions/json_functions/json_search.groovy
b/regression-test/suites/query_p0/sql_functions/json_functions/json_search.groovy
new file mode 100644
index 00000000000..18f606ced9a
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/json_functions/json_search.groovy
@@ -0,0 +1,121 @@
+// 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_search") {
+ def dbName = "test_json_search_db"
+ List<List<Object>> db = sql """show databases like '${dbName}'"""
+ if (db.size() == 0) {
+ sql """CREATE DATABASE ${dbName}"""
+ }
+ sql """use ${dbName}"""
+
+ def testTable = "test_json_search"
+
+ sql """
+ CREATE TABLE `${testTable}` (
+ `id` int NULL,
+ `j` varchar(1000) NULL,
+ `jb` json NULL,
+ `o` varchar(1000) NULL,
+ `p` varchar(1000) NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ DISTRIBUTED BY HASH(`id`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ def jsonValue = """'["A",[{"B":"1"}],{"C":"AB"},{"D":"BC"}]'"""
+
+ sql """insert into ${testTable} values(1, $jsonValue, $jsonValue, NULL,
'_%')"""
+ sql """insert into ${testTable} values(2, $jsonValue, $jsonValue, 'one',
'_%')"""
+ sql """insert into ${testTable} values(3, $jsonValue, $jsonValue, 'One',
'_%')"""
+ sql """insert into ${testTable} values(4, $jsonValue, $jsonValue, 'all',
'_%')"""
+ sql """insert into ${testTable} values(5, $jsonValue, $jsonValue, 'All',
'_%')"""
+ sql """insert into ${testTable} values(6, $jsonValue, $jsonValue,
'invalid_one_or_all', '_%')"""
+ sql """insert into ${testTable} values(7, NULL, NULL, 'one', '_%')"""
+ sql """insert into ${testTable} values(8, $jsonValue, $jsonValue, 'all',
NULL)"""
+ sql """insert into ${testTable} values(9, $jsonValue, $jsonValue, 'all',
'X')"""
+
+ qt_one_is_valid_or_null """ SELECT id, j, o, p, JSON_SEARCH(j, o, p),
JSON_SEARCH(jb, o, p)
+ FROM ${testTable} WHERE o <> 'invalid_one_or_all' ORDER BY
id;"""
+ test {
+ sql """SELECT id, j, o, p, JSON_SEARCH(j, o, p), JSON_SEARCH(jb, o, p)
+ FROM ${testTable} WHERE o = 'invalid_one_or_all' ORDER BY
id;"""
+ exception "the one_or_all argument invalid_one_or_all is not 'one' not
'all'"
+ }
+
+ qt_all_const1 """ SELECT JSON_SEARCH($jsonValue, 'one', '__')"""
+ qt_all_const1 """ SELECT JSON_SEARCH($jsonValue, 'One', '__')"""
+ qt_all_const2 """ SELECT JSON_SEARCH($jsonValue, 'all', '__')"""
+ qt_all_const2 """ SELECT JSON_SEARCH($jsonValue, 'All', '__')"""
+ qt_all_const3 """ SELECT JSON_SEARCH($jsonValue, 'one', 'A')"""
+ qt_all_const4 """ SELECT JSON_SEARCH($jsonValue, 'all', 'A')"""
+ qt_all_const5 """ SELECT JSON_SEARCH($jsonValue, 'one', 'A%')"""
+ qt_all_const6 """ SELECT JSON_SEARCH($jsonValue, 'one', 'A_')"""
+ qt_all_const7 """ SELECT JSON_SEARCH($jsonValue, 'one', 'X')"""
+ qt_all_const8 """ SELECT JSON_SEARCH($jsonValue, 'all', 'X')"""
+
+ qt_one_is_one_const """ SELECT id, j, 'one', p, JSON_SEARCH(j, 'one', p),
JSON_SEARCH(jb, 'one', p)
+ FROM ${testTable} ORDER BY id; """
+ qt_one_is_all_const """ SELECT id, j, 'all', p, JSON_SEARCH(j, 'all', p),
JSON_SEARCH(jb, 'all', p)
+ FROM ${testTable} ORDER BY id; """
+ test {
+ sql """SELECT id, JSON_SEARCH(j, 'invalid_one_or_all', p),
JSON_SEARCH(jb, 'invalid_one_or_all', p)
+ FROM ${testTable} ORDER BY id;"""
+ exception "the one_or_all argument invalid_one_or_all is not 'one' not
'all'"
+ }
+
+ test {
+ sql """SELECT id, JSON_SEARCH(j, o, 'A'), JSON_SEARCH(jb, o, 'A')
+ FROM ${testTable} WHERE o = 'invalid_one_or_all'
ORDER BY id;"""
+ exception "the one_or_all argument invalid_one_or_all is not 'one' not
'all'"
+ }
+
+ test {
+ sql """SELECT id, j, o, p, JSON_SEARCH(j, o, NULL), JSON_SEARCH(jb, o,
NULL)
+ FROM ${testTable} WHERE o = 'invalid_one_or_all'
ORDER BY id;"""
+ exception "the one_or_all argument invalid_one_or_all is not 'one' not
'all'"
+ }
+
+ qt_one_and_pattern_is_const1 """ SELECT id, j, 'one', 'A', JSON_SEARCH(j,
'one', 'A'), JSON_SEARCH(jb, 'one', 'A')
+ FROM ${testTable} ORDER BY id; """
+ qt_one_and_pattern_is_const2 """ SELECT id, j, 'all', 'A', JSON_SEARCH(j,
'all', 'A'), JSON_SEARCH(jb, 'all', 'A')
+ FROM ${testTable} ORDER BY id; """
+
+ qt_one_and_pattern_is_nullconst """ SELECT id, j, NULL, NULL,
JSON_SEARCH(j, NULL, NULL), JSON_SEARCH(jb, NULL, NULL)
+ FROM ${testTable} ORDER BY id; """
+
+ test {
+ sql """ SELECT id, $jsonValue, o, p, JSON_SEARCH($jsonValue, o, p)
FROM ${testTable}
+ WHERE o = 'invalid_one_or_all' ORDER BY id;"""
+ exception "the one_or_all argument invalid_one_or_all is not 'one' not
'all'"
+ }
+ qt_json_const1 """ SELECT id, $jsonValue, 'one', p,
JSON_SEARCH($jsonValue, 'one', p) FROM ${testTable} ORDER BY id; """
+ qt_json_const2 """ SELECT id, $jsonValue, 'all', p,
JSON_SEARCH($jsonValue, 'all', p) FROM ${testTable} ORDER BY id; """
+
+ test {
+ sql """ SELECT id, JSON_SEARCH($jsonValue, o, 'A') FROM ${testTable}
+ WHERE o = 'invalid_one_or_all' ORDER BY id;"""
+ exception "the one_or_all argument invalid_one_or_all is not 'one' not
'all'"
+ }
+
+ qt_one_case1 """ SELECT id, $jsonValue, 'One', p, JSON_SEARCH($jsonValue,
'One', p) FROM ${testTable} ORDER BY id; """
+ qt_one_case2 """ SELECT id, $jsonValue, 'All', p, JSON_SEARCH($jsonValue,
'One', p) FROM ${testTable} ORDER BY id; """
+
+ sql "drop table ${testTable}"
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
