This is an automated email from the ASF dual-hosted git repository.
zclll pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new b34106dcdf7 [Feature](func) Support table function json_each,
json_each_text (#60910)
b34106dcdf7 is described below
commit b34106dcdf709f923275a753073acd23401162ce
Author: linrrarity <[email protected]>
AuthorDate: Fri Mar 27 11:05:21 2026 +0800
[Feature](func) Support table function json_each, json_each_text (#60910)
doc: https://github.com/apache/doris-website/pull/3422
```text
Doris> SELECT k, v
-> FROM (SELECT 1) dummy
-> LATERAL VIEW json_each('{"a":"foo","b":"bar"}') t AS k, v;
+------+-------+
| k | v |
+------+-------+
| a | "foo" |
| b | "bar" |
+------+-------+
2 rows in set (0.01 sec)
Doris> SELECT k, v
-> FROM (SELECT 1) dummy
-> LATERAL VIEW json_each_text('{"a":"foo","b":"bar"}') t AS k, v;
+------+------+
| k | v |
+------+------+
| a | foo |
| b | bar |
+------+------+
2 rows in set (0.01 sec)
```
---
be/src/exprs/function/function_fake.cpp | 40 +-
.../table_function/table_function_factory.cpp | 3 +
be/src/exprs/table_function/vjson_each.cpp | 207 ++++
be/src/exprs/table_function/vjson_each.h | 74 ++
be/test/exprs/function/table_function_test.cpp | 1038 ++++++++++++++++++++
.../catalog/BuiltinTableGeneratingFunctions.java | 10 +
.../expressions/functions/generator/JsonEach.java | 79 ++
.../functions/generator/JsonEachOuter.java | 72 ++
.../functions/generator/JsonEachText.java | 80 ++
.../functions/generator/JsonEachTextOuter.java | 73 ++
.../visitor/TableGeneratingFunctionVisitor.java | 20 +
.../sql_functions/table_function/json_each.out | 174 ++++
.../sql_functions/table_function/json_each.groovy | 419 ++++++++
13 files changed, 2281 insertions(+), 8 deletions(-)
diff --git a/be/src/exprs/function/function_fake.cpp
b/be/src/exprs/function/function_fake.cpp
index 5a5b5735096..0966ed9af61 100644
--- a/be/src/exprs/function/function_fake.cpp
+++ b/be/src/exprs/function/function_fake.cpp
@@ -60,8 +60,7 @@ struct FunctionFakeBaseImpl {
struct FunctionExplode {
static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
- DCHECK(arguments[0]->get_primitive_type() == TYPE_ARRAY)
- << arguments[0]->get_name() << " not supported";
+ DORIS_CHECK(arguments[0]->get_primitive_type() == TYPE_ARRAY);
return make_nullable(
check_and_get_data_type<DataTypeArray>(arguments[0].get())->get_nested_type());
}
@@ -103,8 +102,7 @@ struct FunctionExplodeV2 {
// explode map: make map k,v as struct field
struct FunctionExplodeMap {
static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
- DCHECK(arguments[0]->get_primitive_type() == TYPE_MAP)
- << arguments[0]->get_name() << " not supported";
+ DORIS_CHECK(arguments[0]->get_primitive_type() == TYPE_MAP);
DataTypes fieldTypes(2);
fieldTypes[0] =
check_and_get_data_type<DataTypeMap>(arguments[0].get())->get_key_type();
fieldTypes[1] =
check_and_get_data_type<DataTypeMap>(arguments[0].get())->get_value_type();
@@ -120,8 +118,7 @@ struct FunctionPoseExplode {
DataTypes fieldTypes(arguments.size() + 1);
fieldTypes[0] = std::make_shared<DataTypeInt32>();
for (int i = 0; i < arguments.size(); i++) {
- DCHECK_EQ(arguments[i]->get_primitive_type(), TYPE_ARRAY)
- << arguments[i]->get_name() << " not supported";
+ DORIS_CHECK(arguments[i]->get_primitive_type() == TYPE_ARRAY);
auto nestedType =
check_and_get_data_type<DataTypeArray>(arguments[i].get())->get_nested_type();
fieldTypes[i + 1] = make_nullable(nestedType);
@@ -140,8 +137,7 @@ struct FunctionPoseExplode {
// explode json-object: expands json-object to struct with a pair of key and
value in column string
struct FunctionExplodeJsonObject {
static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
- DCHECK_EQ(arguments[0]->get_primitive_type(),
PrimitiveType::TYPE_JSONB)
- << " explode json object " << arguments[0]->get_name() << "
not supported";
+ DORIS_CHECK(arguments[0]->get_primitive_type() ==
PrimitiveType::TYPE_JSONB);
DataTypes fieldTypes(2);
fieldTypes[0] = make_nullable(std::make_shared<DataTypeString>());
fieldTypes[1] = make_nullable(std::make_shared<DataTypeJsonb>());
@@ -151,6 +147,32 @@ struct FunctionExplodeJsonObject {
static std::string get_error_msg() { return "Fake function do not support
execute"; }
};
+// json_each(json) -> Nullable(Struct(key Nullable(String), value
Nullable(JSONB)))
+struct FunctionJsonEach {
+ static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
+ DORIS_CHECK(arguments[0]->get_primitive_type() ==
PrimitiveType::TYPE_JSONB);
+ DataTypes fieldTypes(2);
+ fieldTypes[0] = make_nullable(std::make_shared<DataTypeString>());
+ fieldTypes[1] = make_nullable(std::make_shared<DataTypeJsonb>());
+ return make_nullable(std::make_shared<DataTypeStruct>(fieldTypes));
+ }
+ static DataTypes get_variadic_argument_types() { return {}; }
+ static std::string get_error_msg() { return "Fake function do not support
execute"; }
+};
+
+// json_each_text(json) -> Nullable(Struct(key Nullable(String), value
Nullable(String)))
+struct FunctionJsonEachText {
+ static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
+ DORIS_CHECK(arguments[0]->get_primitive_type() ==
PrimitiveType::TYPE_JSONB);
+ DataTypes fieldTypes(2);
+ fieldTypes[0] = make_nullable(std::make_shared<DataTypeString>());
+ fieldTypes[1] = make_nullable(std::make_shared<DataTypeString>());
+ return make_nullable(std::make_shared<DataTypeStruct>(fieldTypes));
+ }
+ static DataTypes get_variadic_argument_types() { return {}; }
+ static std::string get_error_msg() { return "Fake function do not support
execute"; }
+};
+
struct FunctionEsquery {
static DataTypePtr get_return_type_impl(const DataTypes& arguments) {
return
FunctionFakeBaseImpl<DataTypeUInt8>::get_return_type_impl(arguments);
@@ -239,6 +261,8 @@ void register_function_fake(SimpleFunctionFactory& factory)
{
register_table_function_expand_outer<FunctionExplodeMap>(factory,
"explode_map");
register_table_function_expand_outer<FunctionExplodeJsonObject>(factory,
"explode_json_object");
+ register_table_function_expand_outer<FunctionJsonEach>(factory,
"json_each");
+ register_table_function_expand_outer<FunctionJsonEachText>(factory,
"json_each_text");
register_table_function_expand_outer_default<DataTypeString,
false>(factory, "explode_split");
register_table_function_expand_outer_default<DataTypeInt32,
false>(factory, "explode_numbers");
register_table_function_expand_outer_default<DataTypeInt64, false>(factory,
diff --git a/be/src/exprs/table_function/table_function_factory.cpp
b/be/src/exprs/table_function/table_function_factory.cpp
index ffe4b8fddd0..4e64fdab1e7 100644
--- a/be/src/exprs/table_function/table_function_factory.cpp
+++ b/be/src/exprs/table_function/table_function_factory.cpp
@@ -34,6 +34,7 @@
#include "exprs/table_function/vexplode_map.h"
#include "exprs/table_function/vexplode_numbers.h"
#include "exprs/table_function/vexplode_v2.h"
+#include "exprs/table_function/vjson_each.h"
namespace doris {
#include "common/compile_check_begin.h"
@@ -50,6 +51,8 @@ const std::unordered_map<std::string,
std::function<std::unique_ptr<TableFunctio
{"explode_bitmap",
TableFunctionCreator<VExplodeBitmapTableFunction>()},
{"explode_map", TableFunctionCreator<VExplodeMapTableFunction>
{}},
{"explode_json_object",
TableFunctionCreator<VExplodeJsonObjectTableFunction> {}},
+ {"json_each", TableFunctionCreator<VJsonEachTableFn> {}},
+ {"json_each_text", TableFunctionCreator<VJsonEachTextTableFn>
{}},
{"posexplode", TableFunctionCreator<VExplodeV2TableFunction>
{}},
{"explode", TableFunctionCreator<VExplodeV2TableFunction> {}},
{"explode_variant_array_old",
TableFunctionCreator<VExplodeTableFunction>()},
diff --git a/be/src/exprs/table_function/vjson_each.cpp
b/be/src/exprs/table_function/vjson_each.cpp
new file mode 100644
index 00000000000..9f42535c7f4
--- /dev/null
+++ b/be/src/exprs/table_function/vjson_each.cpp
@@ -0,0 +1,207 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+#include "exprs/table_function/vjson_each.h"
+
+#include <glog/logging.h>
+
+#include <ostream>
+#include <string>
+
+#include "common/status.h"
+#include "core/assert_cast.h"
+#include "core/block/block.h"
+#include "core/block/column_with_type_and_name.h"
+#include "core/column/column.h"
+#include "core/column/column_const.h"
+#include "core/column/column_struct.h"
+#include "core/string_ref.h"
+#include "exprs/vexpr.h"
+#include "exprs/vexpr_context.h"
+#include "util/jsonb_document.h"
+#include "util/jsonb_utils.h"
+#include "util/jsonb_writer.h"
+
+namespace doris {
+#include "common/compile_check_begin.h"
+
+template <bool TEXT_MODE>
+VJsonEachTableFunction<TEXT_MODE>::VJsonEachTableFunction() {
+ _fn_name = TEXT_MODE ? "vjson_each_text" : "vjson_each";
+}
+
+template <bool TEXT_MODE>
+Status VJsonEachTableFunction<TEXT_MODE>::process_init(Block* block,
RuntimeState* /*state*/) {
+ int value_column_idx = -1;
+
RETURN_IF_ERROR(_expr_context->root()->children()[0]->execute(_expr_context.get(),
block,
+
&value_column_idx));
+ auto [col, is_const] =
unpack_if_const(block->get_by_position(value_column_idx).column);
+ _json_column = col;
+ _is_const = is_const;
+ return Status::OK();
+}
+
+// Helper: insert one JsonbValue as plain text into a
ColumnNullable<ColumnString>.
+// For strings: raw blob content (quotes stripped, matching json_each_text PG
semantics).
+// For null JSON values: SQL NULL (insert_default).
+// For all others (numbers, bools, objects, arrays): JSON text representation.
+static void insert_value_as_text(const JsonbValue* value, MutableColumnPtr&
col) {
+ if (value == nullptr || value->isNull()) {
+ col->insert_default();
+ return;
+ }
+ if (value->isString()) {
+ const auto* str_val = value->unpack<JsonbStringVal>();
+ col->insert_data(str_val->getBlob(), str_val->getBlobLen());
+ } else {
+ JsonbToJson converter;
+ std::string text = converter.to_json_string(value);
+ col->insert_data(text.data(), text.size());
+ }
+}
+
+// Helper: insert one JsonbValue in JSONB binary form into a
ColumnNullable<ColumnString>.
+// For null JSON values: SQL NULL (insert_default).
+// For all others: write JSONB binary via JsonbWriter.
+static void insert_value_as_json(const JsonbValue* value, MutableColumnPtr&
col,
+ JsonbWriter& writer) {
+ if (value == nullptr || value->isNull()) {
+ col->insert_default();
+ return;
+ }
+ writer.reset();
+ writer.writeValue(value);
+ const auto* buf = writer.getOutput()->getBuffer();
+ size_t len = writer.getOutput()->getSize();
+ col->insert_data(buf, len);
+}
+
+template <bool TEXT_MODE>
+void VJsonEachTableFunction<TEXT_MODE>::process_row(size_t row_idx) {
+ TableFunction::process_row(row_idx);
+ if (_is_const && _cur_size > 0) {
+ return;
+ }
+
+ StringRef text;
+ const size_t idx = _is_const ? 0 : row_idx;
+ if (const auto* nullable_col =
check_and_get_column<ColumnNullable>(*_json_column)) {
+ if (nullable_col->is_null_at(idx)) {
+ return;
+ }
+ text = assert_cast<const
ColumnString&>(nullable_col->get_nested_column()).get_data_at(idx);
+ } else {
+ text = assert_cast<const
ColumnString&>(*_json_column).get_data_at(idx);
+ }
+
+ const JsonbDocument* doc = nullptr;
+ auto st = JsonbDocument::checkAndCreateDocument(text.data, text.size,
&doc);
+ if (!st.ok() || !doc || !doc->getValue()) [[unlikely]] {
+ return;
+ }
+
+ const JsonbValue* jv = doc->getValue();
+ if (!jv->isObject()) {
+ return;
+ }
+
+ const auto* obj = jv->unpack<ObjectVal>();
+ _cur_size = obj->numElem();
+ if (_cur_size == 0) {
+ return;
+ }
+
+ _kv_pairs.first = ColumnNullable::create(ColumnString::create(),
ColumnUInt8::create());
+ _kv_pairs.second = ColumnNullable::create(ColumnString::create(),
ColumnUInt8::create());
+ _kv_pairs.first->reserve(_cur_size);
+ _kv_pairs.second->reserve(_cur_size);
+
+ if constexpr (TEXT_MODE) {
+ for (const auto& kv : *obj) {
+ _kv_pairs.first->insert_data(kv.getKeyStr(), kv.klen());
+ insert_value_as_text(kv.value(), _kv_pairs.second);
+ }
+ } else {
+ JsonbWriter writer;
+ for (const auto& kv : *obj) {
+ _kv_pairs.first->insert_data(kv.getKeyStr(), kv.klen());
+ insert_value_as_json(kv.value(), _kv_pairs.second, writer);
+ }
+ }
+}
+
+template <bool TEXT_MODE>
+void VJsonEachTableFunction<TEXT_MODE>::process_close() {
+ _json_column = nullptr;
+ _kv_pairs.first = nullptr;
+ _kv_pairs.second = nullptr;
+ _cur_size = 0;
+}
+
+template <bool TEXT_MODE>
+void VJsonEachTableFunction<TEXT_MODE>::get_same_many_values(MutableColumnPtr&
column, int length) {
+ if (current_empty()) {
+ column->insert_many_defaults(length);
+ return;
+ }
+
+ ColumnStruct* ret;
+ if (_is_nullable) {
+ auto* nullable = assert_cast<ColumnNullable*>(column.get());
+ ret =
assert_cast<ColumnStruct*>(nullable->get_nested_column_ptr().get());
+ assert_cast<ColumnUInt8*>(nullable->get_null_map_column_ptr().get())
+ ->insert_many_defaults(length);
+ } else {
+ ret = assert_cast<ColumnStruct*>(column.get());
+ }
+
+ ret->get_column(0).insert_many_from(*_kv_pairs.first, _cur_offset, length);
+ ret->get_column(1).insert_many_from(*_kv_pairs.second, _cur_offset,
length);
+}
+
+template <bool TEXT_MODE>
+int VJsonEachTableFunction<TEXT_MODE>::get_value(MutableColumnPtr& column, int
max_step) {
+ max_step = std::min(max_step, (int)(_cur_size - _cur_offset));
+
+ if (current_empty()) {
+ column->insert_default();
+ max_step = 1;
+ } else {
+ ColumnStruct* struct_col = nullptr;
+ if (_is_nullable) {
+ auto* nullable_col = assert_cast<ColumnNullable*>(column.get());
+ struct_col =
assert_cast<ColumnStruct*>(nullable_col->get_nested_column_ptr().get());
+
assert_cast<ColumnUInt8*>(nullable_col->get_null_map_column_ptr().get())
+ ->insert_many_defaults(max_step);
+ } else {
+ struct_col = assert_cast<ColumnStruct*>(column.get());
+ }
+
+ struct_col->get_column(0).insert_range_from(*_kv_pairs.first,
_cur_offset, max_step);
+ struct_col->get_column(1).insert_range_from(*_kv_pairs.second,
_cur_offset, max_step);
+ }
+
+ forward(max_step);
+ return max_step;
+}
+
+// // Explicit template instantiations
+template class VJsonEachTableFunction<false>; // json_each
+template class VJsonEachTableFunction<true>; // json_each_text
+
+#include "common/compile_check_end.h"
+} // namespace doris
diff --git a/be/src/exprs/table_function/vjson_each.h
b/be/src/exprs/table_function/vjson_each.h
new file mode 100644
index 00000000000..830e44d3058
--- /dev/null
+++ b/be/src/exprs/table_function/vjson_each.h
@@ -0,0 +1,74 @@
+// 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.
+
+#pragma once
+
+#include <cstddef>
+
+#include "common/status.h"
+#include "core/data_type/data_type.h"
+#include "exprs/table_function/table_function.h"
+
+namespace doris {
+#include "common/compile_check_begin.h"
+class Block;
+
+// json_each('{"a":"foo","b":123}') →
+// | key | value |
+// | a | "foo" (JSON) |
+// | b | 123 (JSON) |
+//
+// json_each_text('{"a":"foo","b":123}') →
+// | key | value |
+// | a | foo | ← string unquoted
+// | b | 123 | ← number as text
+//
+// TEXT_MODE=false → json_each (value column type: JSONB binary)
+// TEXT_MODE=true → json_each_text (value column type: plain STRING)
+template <bool TEXT_MODE>
+class VJsonEachTableFunction : public TableFunction {
+ ENABLE_FACTORY_CREATOR(VJsonEachTableFunction);
+
+public:
+ VJsonEachTableFunction();
+
+ ~VJsonEachTableFunction() override = default;
+
+ Status process_init(Block* block, RuntimeState* state) override;
+ void process_row(size_t row_idx) override;
+ void process_close() override;
+ void get_same_many_values(MutableColumnPtr& column, int length) override;
+ int get_value(MutableColumnPtr& column, int max_step) override;
+
+#ifdef BE_TEST
+ const ColumnPtr& test_json_column() const { return _json_column; }
+ const MutableColumnPtr& test_kv_pairs_first() const { return
_kv_pairs.first; }
+ const MutableColumnPtr& test_kv_pairs_second() const { return
_kv_pairs.second; }
+#endif
+
+private:
+ ColumnPtr _json_column;
+ // _kv_pairs.first : ColumnNullable<ColumnString> key (always plain text)
+ // _kv_pairs.second : ColumnNullable<ColumnString> value (JSONB bytes or
plain text)
+ std::pair<MutableColumnPtr, MutableColumnPtr> _kv_pairs;
+};
+
+using VJsonEachTableFn = VJsonEachTableFunction<false>;
+using VJsonEachTextTableFn = VJsonEachTableFunction<true>;
+
+#include "common/compile_check_end.h"
+} // namespace doris
diff --git a/be/test/exprs/function/table_function_test.cpp
b/be/test/exprs/function/table_function_test.cpp
index 09056d829a9..5ae08c411ac 100644
--- a/be/test/exprs/function/table_function_test.cpp
+++ b/be/test/exprs/function/table_function_test.cpp
@@ -30,7 +30,11 @@
#include "exprs/table_function/vexplode.h"
#include "exprs/table_function/vexplode_numbers.h"
#include "exprs/table_function/vexplode_v2.h"
+#include "exprs/table_function/vjson_each.h"
#include "testutil/any_type.h"
+#include "util/jsonb_parser_simd.h"
+#include "util/jsonb_utils.h"
+#include "util/jsonb_writer.h"
namespace doris {
@@ -308,4 +312,1038 @@ TEST_F(TableFunctionTest, vexplode_numbers) {
}
}
+// ---------------------------------------------------------------------------
+// Direct-API helpers for json_each / json_each_text tests.
+// The test framework's check_vec_table_function does not properly support
+// TYPE_STRUCT output (insert_cell always expects ColumnNullable wrapping the
+// struct column), so we drive the table function API directly.
+// ---------------------------------------------------------------------------
+
+// Build a one-column JSONB input block. An empty string means SQL NULL.
+static std::unique_ptr<Block> build_jsonb_input_block(const
std::vector<std::string>& json_rows) {
+ auto str_col = ColumnString::create();
+ auto null_col = ColumnUInt8::create();
+ for (const auto& json : json_rows) {
+ if (json.empty()) {
+ str_col->insert_default();
+ null_col->insert_value(1);
+ } else {
+ JsonbWriter writer;
+ if (JsonbParser::parse(json.c_str(), json.size(), writer).ok()) {
+ str_col->insert_data(writer.getOutput()->getBuffer(),
+ writer.getOutput()->getSize());
+ null_col->insert_value(0);
+ } else {
+ str_col->insert_default();
+ null_col->insert_value(1);
+ }
+ }
+ }
+ auto col = ColumnNullable::create(std::move(str_col), std::move(null_col));
+ auto block = Block::create_unique();
+ block->insert({std::move(col),
+ make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_JSONB, false)),
+ "jval"});
+ return block;
+}
+
+// Run the given table function over all rows in block.
+// Returns list of (key, value) pairs where value == "__NULL__" means SQL NULL.
+// val_is_jsonb controls whether the value column is decoded as JSONB→JSON
text or plain text.
+static std::vector<std::pair<std::string, std::string>>
run_json_each_fn(TableFunction* fn,
+
Block* block,
+ bool
val_is_jsonb) {
+ // Output type: Nullable(Struct(Nullable(VARCHAR key),
Nullable(VARCHAR/JSONB value)))
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ val_is_jsonb ? doris::PrimitiveType::TYPE_JSONB :
doris::PrimitiveType::TYPE_VARCHAR,
+ false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ auto out_col = struct_dt->create_column();
+ fn->set_nullable();
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+ EXPECT_TRUE(fn->process_init(block, &rs).ok());
+
+ for (size_t row = 0; row < block->rows(); ++row) {
+ fn->process_row(row);
+ if (!fn->current_empty()) {
+ do {
+ fn->get_value(out_col, 1);
+ } while (!fn->eos());
+ }
+ }
+ fn->process_close();
+
+ std::vector<std::pair<std::string, std::string>> result;
+ const auto& nullable_out = assert_cast<const ColumnNullable&>(*out_col);
+ const auto& struct_col = assert_cast<const
ColumnStruct&>(nullable_out.get_nested_column());
+ const auto& key_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(0));
+ const auto& val_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(1));
+
+ for (size_t i = 0; i < struct_col.size(); ++i) {
+ if (nullable_out.is_null_at(i)) {
+ result.emplace_back("__NULL_ROW__", "__NULL_ROW__");
+ continue;
+ }
+ std::string key;
+ if (!key_col.is_null_at(i)) {
+ StringRef sr = key_col.get_nested_column().get_data_at(i);
+ key.assign(sr.data, sr.size);
+ }
+ std::string val;
+ if (val_col.is_null_at(i)) {
+ val = "__NULL__";
+ } else {
+ StringRef sr = val_col.get_nested_column().get_data_at(i);
+ if (val_is_jsonb) {
+ // JSONB binary → JSON text for comparison
+ const JsonbDocument* doc = nullptr;
+ if (JsonbDocument::checkAndCreateDocument(sr.data, sr.size,
&doc).ok() && doc &&
+ doc->getValue()) {
+ JsonbToJson converter;
+ val = converter.to_json_string(doc->getValue());
+ } else {
+ val = "__BAD_JSONB__";
+ }
+ } else {
+ val.assign(sr.data, sr.size);
+ }
+ }
+ result.emplace_back(std::move(key), std::move(val));
+ }
+ return result;
+}
+
+TEST_F(TableFunctionTest, vjson_each) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ // basic: string and numeric values; JSONB value column shows JSON text
with quotes
+ {
+ auto block = build_jsonb_input_block({{R"({"a":"foo","b":123})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ ASSERT_EQ(2u, rows.size());
+ EXPECT_EQ("a", rows[0].first);
+ EXPECT_EQ("\"foo\"", rows[0].second); // JSONB string → JSON text
includes quotes
+ EXPECT_EQ("b", rows[1].first);
+ EXPECT_EQ("123", rows[1].second);
+ }
+
+ // JSON null value → SQL NULL
+ {
+ auto block = build_jsonb_input_block({{R"({"x":null})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ ASSERT_EQ(1u, rows.size());
+ EXPECT_EQ("x", rows[0].first);
+ EXPECT_EQ("__NULL__", rows[0].second);
+ }
+
+ // boolean and negative int
+ {
+ auto block = build_jsonb_input_block({{R"({"flag":true,"neg":-1})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ ASSERT_EQ(2u, rows.size());
+ bool ok_flag = false, ok_neg = false;
+ for (auto& kv : rows) {
+ if (kv.first == "flag") {
+ EXPECT_EQ("true", kv.second);
+ ok_flag = true;
+ }
+ if (kv.first == "neg") {
+ EXPECT_EQ("-1", kv.second);
+ ok_neg = true;
+ }
+ }
+ EXPECT_TRUE(ok_flag) << "key 'flag' not found";
+ EXPECT_TRUE(ok_neg) << "key 'neg' not found";
+ }
+
+ // SQL NULL input → 0 rows
+ {
+ auto block = build_jsonb_input_block({{""}}); // empty string → SQL
NULL
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ EXPECT_EQ(0u, rows.size());
+ }
+
+ // empty object → 0 rows
+ {
+ auto block = build_jsonb_input_block({{"{}"}});
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ EXPECT_EQ(0u, rows.size());
+ }
+
+ // non-object input → 0 rows
+ {
+ auto block = build_jsonb_input_block({{"[1,2,3]"}});
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ EXPECT_EQ(0u, rows.size());
+ }
+}
+
+TEST_F(TableFunctionTest, vjson_each_text) {
+ init_expr_context(1);
+ VJsonEachTextTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ // basic: strings unquoted (text mode), numbers as plain text
+ {
+ auto block = build_jsonb_input_block({{R"({"a":"foo","b":123})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), false);
+ ASSERT_EQ(2u, rows.size());
+ EXPECT_EQ("a", rows[0].first);
+ EXPECT_EQ("foo", rows[0].second); // string unquoted in text mode
+ EXPECT_EQ("b", rows[1].first);
+ EXPECT_EQ("123", rows[1].second);
+ }
+
+ // booleans
+ {
+ auto block = build_jsonb_input_block({{R"({"t":true,"f":false})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), false);
+ ASSERT_EQ(2u, rows.size());
+ bool ok_t = false, ok_f = false;
+ for (auto& kv : rows) {
+ if (kv.first == "t") {
+ EXPECT_EQ("true", kv.second);
+ ok_t = true;
+ }
+ if (kv.first == "f") {
+ EXPECT_EQ("false", kv.second);
+ ok_f = true;
+ }
+ }
+ EXPECT_TRUE(ok_t) << "key 't' not found";
+ EXPECT_TRUE(ok_f) << "key 'f' not found";
+ }
+
+ // JSON null → SQL NULL
+ {
+ auto block = build_jsonb_input_block({{R"({"x":null})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), false);
+ ASSERT_EQ(1u, rows.size());
+ EXPECT_EQ("x", rows[0].first);
+ EXPECT_EQ("__NULL__", rows[0].second);
+ }
+
+ // SQL NULL input → 0 rows
+ {
+ auto block = build_jsonb_input_block({{""}});
+ auto rows = run_json_each_fn(&fn, block.get(), false);
+ EXPECT_EQ(0u, rows.size());
+ }
+
+ // empty object → 0 rows
+ {
+ auto block = build_jsonb_input_block({{"{}"}});
+ auto rows = run_json_each_fn(&fn, block.get(), false);
+ EXPECT_EQ(0u, rows.size());
+ }
+}
+TEST_F(TableFunctionTest, vjson_each_get_same_many_values) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ // Case 1: normal object — get_same_many_values replicates the entry at
_cur_offset.
+ // Simulates a non-last table function being asked to copy its current
value 3 times
+ // to match 3 rows emitted by the driving (last) function in the same pass.
+ {
+ auto block = build_jsonb_input_block({{R"({"k0":"v0","k1":"v1"})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ fn.get_same_many_values(out_col, 3);
+
+ const auto& nullable_out = assert_cast<const
ColumnNullable&>(*out_col);
+ ASSERT_EQ(3u, nullable_out.size());
+ const auto& struct_col = assert_cast<const
ColumnStruct&>(nullable_out.get_nested_column());
+ const auto& key_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(0));
+ // All 3 output rows should carry the entry at _cur_offset=0 ("k0")
+ for (size_t i = 0; i < 3; ++i) {
+ EXPECT_FALSE(nullable_out.is_null_at(i));
+ ASSERT_FALSE(key_col.is_null_at(i));
+ StringRef k = key_col.get_nested_column().get_data_at(i);
+ EXPECT_EQ("k0", std::string(k.data, k.size));
+ }
+ fn.process_close();
+ }
+
+ // Case 2: SQL NULL input — current_empty() is true → insert_many_defaults.
+ {
+ auto block = build_jsonb_input_block({{""}}); // empty string → SQL
NULL
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_TRUE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ fn.get_same_many_values(out_col, 2);
+
+ ASSERT_EQ(2u, out_col->size());
+ const auto& nullable_out = assert_cast<const
ColumnNullable&>(*out_col);
+ EXPECT_TRUE(nullable_out.is_null_at(0));
+ EXPECT_TRUE(nullable_out.is_null_at(1));
+ fn.process_close();
+ }
+}
+
+TEST_F(TableFunctionTest, vjson_each_outer) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ // set_outer() correctly sets the is_outer flag
+ EXPECT_FALSE(fn.is_outer());
+ fn.set_outer();
+ EXPECT_TRUE(fn.is_outer());
+
+ // Normal object: outer flag does not affect KV expansion
+ {
+ auto block = build_jsonb_input_block({{R"({"a":"foo","b":123})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), true);
+ ASSERT_EQ(2u, rows.size());
+ EXPECT_EQ("a", rows[0].first);
+ EXPECT_EQ("\"foo\"", rows[0].second);
+ EXPECT_EQ("b", rows[1].first);
+ EXPECT_EQ("123", rows[1].second);
+ }
+
+ // For NULL / empty-object / non-object inputs: current_empty() is true.
+ // The operator calls get_value() unconditionally when is_outer() — verify
that
+ // get_value() inserts exactly one default (NULL) struct row in each case.
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ for (const char* input : {"", "{}", "[1,2,3]"}) {
+ auto block = build_jsonb_input_block({{input}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok()) << "input: " <<
input;
+ fn.process_row(0);
+ EXPECT_TRUE(fn.current_empty()) << "input: " << input;
+
+ auto out_col = struct_dt->create_column();
+ fn.get_value(out_col, 1);
+ ASSERT_EQ(1u, out_col->size()) << "input: " << input;
+ EXPECT_TRUE(out_col->is_null_at(0)) << "input: " << input;
+ fn.process_close();
+ }
+}
+
+TEST_F(TableFunctionTest, vjson_each_text_outer) {
+ init_expr_context(1);
+ VJsonEachTextTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ EXPECT_FALSE(fn.is_outer());
+ fn.set_outer();
+ EXPECT_TRUE(fn.is_outer());
+
+ // Normal object: text mode (strings unquoted), outer flag does not affect
expansion
+ {
+ auto block = build_jsonb_input_block({{R"({"a":"foo","b":123})"}});
+ auto rows = run_json_each_fn(&fn, block.get(), false);
+ ASSERT_EQ(2u, rows.size());
+ EXPECT_EQ("a", rows[0].first);
+ EXPECT_EQ("foo", rows[0].second);
+ EXPECT_EQ("b", rows[1].first);
+ EXPECT_EQ("123", rows[1].second);
+ }
+
+ // NULL / empty-object / non-object → current_empty(), get_value() inserts
one default row
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ for (const char* input : {"", "{}", "[1,2,3]"}) {
+ auto block = build_jsonb_input_block({{input}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok()) << "input: " <<
input;
+ fn.process_row(0);
+ EXPECT_TRUE(fn.current_empty()) << "input: " << input;
+
+ auto out_col = struct_dt->create_column();
+ fn.get_value(out_col, 1);
+ ASSERT_EQ(1u, out_col->size()) << "input: " << input;
+ EXPECT_TRUE(out_col->is_null_at(0)) << "input: " << input;
+ fn.process_close();
+ }
+}
+
+// Helper: build a one-column JSONB block with raw bytes, bypassing JSON parse.
+static std::unique_ptr<Block> build_raw_jsonb_block(
+ const std::vector<std::pair<std::string, bool>>& entries) {
+ auto str_col = ColumnString::create();
+ auto null_col = ColumnUInt8::create();
+ for (const auto& [data, is_null] : entries) {
+ if (is_null) {
+ str_col->insert_default();
+ null_col->insert_value(1);
+ } else {
+ str_col->insert_data(data.data(), data.size());
+ null_col->insert_value(0);
+ }
+ }
+ auto col = ColumnNullable::create(std::move(str_col), std::move(null_col));
+ auto block = Block::create_unique();
+ block->insert({std::move(col),
+ make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_JSONB, false)),
+ "jval"});
+ return block;
+}
+
+// Corrupt JSONB binary input — hits checkAndCreateDocument failure branch in
process_row.
+TEST_F(TableFunctionTest, vjson_each_corrupt_jsonb_binary) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ // Garbage bytes marked as non-null — checkAndCreateDocument should fail,
+ // process_row should leave _cur_size=0 (current_empty() == true).
+ std::string garbage = "\xDE\xAD\xBE\xEF";
+ auto block = build_raw_jsonb_block({{garbage, false}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+
+ // Directly verify internal state via BE_TEST-exposed members
+ EXPECT_TRUE(fn.current_empty());
+ EXPECT_TRUE(!fn.test_kv_pairs_first());
+ EXPECT_TRUE(!fn.test_kv_pairs_second());
+
+ fn.process_close();
+}
+
+// Corrupt JSONB + outer mode → get_value should insert exactly one default
(NULL) row.
+TEST_F(TableFunctionTest, vjson_each_corrupt_jsonb_outer) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_outer();
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ std::string garbage = "\x00\x01\x02\x03";
+ auto block = build_raw_jsonb_block({{garbage, false}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ EXPECT_TRUE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ fn.get_value(out_col, 1);
+ ASSERT_EQ(1U, out_col->size());
+ EXPECT_TRUE(out_col->is_null_at(0)); // outer: one default NULL row
+
+ fn.process_close();
+}
+
+// Corrupt JSONB in json_each_text mode — same behaviour.
+TEST_F(TableFunctionTest, vjson_each_text_corrupt_jsonb_binary) {
+ init_expr_context(1);
+ VJsonEachTextTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ std::string garbage = "\xFF\xFE\xFD";
+ auto block = build_raw_jsonb_block({{garbage, false}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+
+ EXPECT_TRUE(fn.current_empty());
+ EXPECT_TRUE(!fn.test_kv_pairs_first());
+ EXPECT_TRUE(!fn.test_kv_pairs_second());
+
+ fn.process_close();
+}
+
+// Corrupt JSONB in json_each_text_outer mode should still emit one default
row.
+TEST_F(TableFunctionTest, vjson_each_text_corrupt_jsonb_outer) {
+ init_expr_context(1);
+ VJsonEachTextTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_outer();
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ std::string garbage = "\xFA\xFB\xFC";
+ auto block = build_raw_jsonb_block({{garbage, false}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ EXPECT_TRUE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ fn.get_value(out_col, 1);
+ ASSERT_EQ(1U, out_col->size());
+ EXPECT_TRUE(out_col->is_null_at(0));
+
+ fn.process_close();
+}
+
+// get_same_many_values at non-zero offset — verify it copies current offset
entry,
+// not the first entry.
+TEST_F(TableFunctionTest, vjson_each_get_same_many_values_nonzero_offset) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ // Object with 3 keys: k0, k1, k2
+ auto block =
build_jsonb_input_block({{R"({"k0":"v0","k1":"v1","k2":"v2"})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ // Consume the first entry via get_value(max_step=1) to advance
_cur_offset to 1
+ {
+ auto tmp_col = struct_dt->create_column();
+ int step = fn.get_value(tmp_col, 1);
+ EXPECT_EQ(1, step);
+ EXPECT_FALSE(fn.eos());
+ }
+
+ // Now _cur_offset == 1. get_same_many_values should replicate entry at
offset 1 ("k1").
+ auto out_col = struct_dt->create_column();
+ fn.get_same_many_values(out_col, 3);
+
+ const auto& nullable_out = assert_cast<const ColumnNullable&>(*out_col);
+ ASSERT_EQ(3U, nullable_out.size());
+ const auto& struct_col = assert_cast<const
ColumnStruct&>(nullable_out.get_nested_column());
+ const auto& key_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(0));
+ for (size_t i = 0; i < 3; ++i) {
+ EXPECT_FALSE(nullable_out.is_null_at(i));
+ ASSERT_FALSE(key_col.is_null_at(i));
+ StringRef k = key_col.get_nested_column().get_data_at(i);
+ EXPECT_EQ("k1", std::string(k.data, k.size))
+ << "Expected entry at offset 1, got '" << std::string(k.data,
k.size) << "'";
+ }
+ fn.process_close();
+}
+
+// Same test for json_each_text mode.
+TEST_F(TableFunctionTest, vjson_each_text_get_same_many_values_nonzero_offset)
{
+ init_expr_context(1);
+ VJsonEachTextTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"a":"A","b":"B","c":"C"})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ // Advance offset past first entry
+ {
+ auto tmp_col = struct_dt->create_column();
+ fn.get_value(tmp_col, 1);
+ }
+
+ auto out_col = struct_dt->create_column();
+ fn.get_same_many_values(out_col, 2);
+
+ const auto& nullable_out = assert_cast<const ColumnNullable&>(*out_col);
+ ASSERT_EQ(2U, nullable_out.size());
+ const auto& struct_col = assert_cast<const
ColumnStruct&>(nullable_out.get_nested_column());
+ const auto& key_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(0));
+ const auto& val_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(1));
+ for (size_t i = 0; i < 2; ++i) {
+ StringRef k = key_col.get_nested_column().get_data_at(i);
+ EXPECT_EQ("b", std::string(k.data, k.size)); // offset 1 = "b"
+ StringRef v = val_col.get_nested_column().get_data_at(i);
+ EXPECT_EQ("B", std::string(v.data, v.size)); // text mode: unquoted
+ }
+ fn.process_close();
+}
+
+// process_close — directly verify private members are reset.
+TEST_F(TableFunctionTest, vjson_each_process_close_internal_state) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"a":1,"b":2})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+
+ // Before close: members should be populated
+ EXPECT_TRUE(fn.test_json_column());
+ EXPECT_TRUE(fn.test_kv_pairs_first());
+ EXPECT_TRUE(fn.test_kv_pairs_second());
+ EXPECT_FALSE(fn.current_empty());
+
+ fn.process_close();
+
+ // After close: all pointers null, _cur_size reset
+ EXPECT_TRUE(!fn.test_json_column());
+ EXPECT_TRUE(!fn.test_kv_pairs_first());
+ EXPECT_TRUE(!fn.test_kv_pairs_second());
+ EXPECT_TRUE(fn.current_empty());
+}
+
+// process_row with _is_const — second call should skip re-parsing when
_cur_size > 0.
+// Verify by inspecting _kv_pairs: they should remain from the first call.
+TEST_F(TableFunctionTest, vjson_each_process_row_const_column) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ // Build a const column (ColumnConst wrapping a single JSONB value)
+ JsonbWriter writer;
+ const std::string json_const_obj = R"({"x":10,"y":20})";
+ ASSERT_TRUE(JsonbParser::parse(json_const_obj.data(),
json_const_obj.size(), writer).ok());
+ auto inner_str_col = ColumnString::create();
+ auto inner_null_col = ColumnUInt8::create();
+ inner_str_col->insert_data(writer.getOutput()->getBuffer(),
writer.getOutput()->getSize());
+ inner_null_col->insert_value(0);
+ auto inner_nullable =
+ ColumnNullable::create(std::move(inner_str_col),
std::move(inner_null_col));
+ // Wrap as ColumnConst with 3 logical rows
+ auto const_col = ColumnConst::create(std::move(inner_nullable), 3);
+
+ auto block = Block::create_unique();
+ block->insert({std::move(const_col),
+ make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_JSONB, false)),
+ "jval"});
+
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+
+ // First process_row: parses and populates _kv_pairs
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+ auto* kv_first_ptr = fn.test_kv_pairs_first().get();
+ ASSERT_NE(nullptr, kv_first_ptr);
+
+ // Reset offset to simulate next iteration (the operator resets between
rows)
+ fn.reset();
+
+ // Second process_row on a different logical row: should skip reparsing
(_is_const && _cur_size>0)
+ fn.process_row(1);
+ EXPECT_FALSE(fn.current_empty());
+ // _kv_pairs.first pointer should be identical — no re-allocation
+ EXPECT_EQ(kv_first_ptr, fn.test_kv_pairs_first().get());
+
+ fn.process_close();
+}
+
+TEST_F(TableFunctionTest, vjson_each_process_row_const_empty_object_column) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ JsonbWriter writer;
+ const std::string json_empty_obj = R"({})";
+ ASSERT_TRUE(JsonbParser::parse(json_empty_obj.data(),
json_empty_obj.size(), writer).ok());
+ auto inner_str_col = ColumnString::create();
+ auto inner_null_col = ColumnUInt8::create();
+ inner_str_col->insert_data(writer.getOutput()->getBuffer(),
writer.getOutput()->getSize());
+ inner_null_col->insert_value(0);
+ auto inner_nullable =
+ ColumnNullable::create(std::move(inner_str_col),
std::move(inner_null_col));
+ auto const_col = ColumnConst::create(std::move(inner_nullable), 2);
+
+ auto block = Block::create_unique();
+ block->insert({std::move(const_col),
+ make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_JSONB, false)),
+ "jval"});
+
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+
+ fn.process_row(0);
+ EXPECT_TRUE(fn.current_empty());
+ EXPECT_TRUE(!fn.test_kv_pairs_first());
+ EXPECT_TRUE(!fn.test_kv_pairs_second());
+
+ fn.reset();
+ fn.process_row(1);
+ EXPECT_TRUE(fn.current_empty());
+ EXPECT_TRUE(!fn.test_kv_pairs_first());
+ EXPECT_TRUE(!fn.test_kv_pairs_second());
+
+ fn.process_close();
+}
+
+// get_value on current_empty — inserts exactly one default and returns 1.
+TEST_F(TableFunctionTest, vjson_each_get_value_current_empty) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ // Empty object → current_empty
+ auto block = build_jsonb_input_block({{"{}"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ EXPECT_TRUE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ int step = fn.get_value(out_col, 5); // max_step ignored when empty
+ EXPECT_EQ(1, step);
+ ASSERT_EQ(1U, out_col->size());
+ EXPECT_TRUE(out_col->is_null_at(0)); // default row is NULL struct
+ EXPECT_TRUE(fn.eos());
+
+ fn.process_close();
+}
+
+// get_value with max_step > _cur_size — clamped to actual size.
+TEST_F(TableFunctionTest, vjson_each_get_value_max_step_clamped) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"a":1,"b":2})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ int step = fn.get_value(out_col, 100); // request 100, only 2 available
+ EXPECT_EQ(2, step);
+ ASSERT_EQ(2U, out_col->size());
+ EXPECT_TRUE(fn.eos());
+
+ fn.process_close();
+}
+
+TEST_F(TableFunctionTest, vjson_each_get_value_zero_max_step) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ fn.set_nullable();
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt =
+ make_nullable(std::make_shared<DataTypeStruct>(DataTypes {key_dt,
val_dt}));
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"a":1,"b":2})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ auto out_col = struct_dt->create_column();
+ int step = fn.get_value(out_col, 0);
+ EXPECT_EQ(0, step);
+ EXPECT_EQ(0U, out_col->size());
+ EXPECT_FALSE(fn.eos());
+
+ fn.process_close();
+}
+
+// Verify _kv_pairs content directly after process_row (json_each mode) —
+// value column should contain JSONB binary, not JSON text.
+TEST_F(TableFunctionTest, vjson_each_kv_pairs_jsonb_binary) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"k":"hello"})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ // Inspect key column
+ const auto& key_col = assert_cast<const
ColumnNullable&>(*fn.test_kv_pairs_first());
+ ASSERT_EQ(1U, key_col.size());
+ ASSERT_FALSE(key_col.is_null_at(0));
+ StringRef key = key_col.get_nested_column().get_data_at(0);
+ EXPECT_EQ("k", std::string(key.data, key.size));
+
+ // Inspect value column — should be valid JSONB binary, not plain text
+ const auto& val_col = assert_cast<const
ColumnNullable&>(*fn.test_kv_pairs_second());
+ ASSERT_EQ(1U, val_col.size());
+ ASSERT_FALSE(val_col.is_null_at(0));
+ StringRef val_raw = val_col.get_nested_column().get_data_at(0);
+ // Verify it's valid JSONB by parsing it back
+ const JsonbDocument* doc = nullptr;
+ ASSERT_TRUE(JsonbDocument::checkAndCreateDocument(val_raw.data,
val_raw.size, &doc).ok());
+ ASSERT_NE(nullptr, doc);
+ ASSERT_NE(nullptr, doc->getValue());
+ EXPECT_TRUE(doc->getValue()->isString());
+
+ fn.process_close();
+}
+
+// Verify _kv_pairs content directly after process_row (json_each_text mode) —
+// string values should be raw blob content (unquoted), not JSONB binary.
+TEST_F(TableFunctionTest, vjson_each_text_kv_pairs_plain_text) {
+ init_expr_context(1);
+ VJsonEachTextTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"k":"hello","n":42})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ const auto& val_col = assert_cast<const
ColumnNullable&>(*fn.test_kv_pairs_second());
+ ASSERT_EQ(2U, val_col.size());
+
+ // Find the entries (order depends on JSONB iteration)
+ std::map<std::string, std::string> kv;
+ const auto& key_col = assert_cast<const
ColumnNullable&>(*fn.test_kv_pairs_first());
+ for (size_t i = 0; i < 2; ++i) {
+ StringRef kr = key_col.get_nested_column().get_data_at(i);
+ StringRef vr = val_col.get_nested_column().get_data_at(i);
+ kv[std::string(kr.data, kr.size)] = std::string(vr.data, vr.size);
+ }
+ // Text mode: string "hello" unquoted, number "42" as plain text
+ EXPECT_EQ("hello", kv["k"]);
+ EXPECT_EQ("42", kv["n"]);
+
+ fn.process_close();
+}
+
+// Verify _kv_pairs for JSON null value — should produce SQL NULL (is_null_at
== true).
+TEST_F(TableFunctionTest, vjson_each_kv_pairs_null_value) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"k":null})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ const auto& val_col = assert_cast<const
ColumnNullable&>(*fn.test_kv_pairs_second());
+ ASSERT_EQ(1U, val_col.size());
+ EXPECT_TRUE(val_col.is_null_at(0)); // JSON null → SQL NULL via
insert_default
+
+ fn.process_close();
+}
+
+// forward() and eos() interaction — test the base class forward logic
+// through the json_each function.
+TEST_F(TableFunctionTest, vjson_each_forward_eos) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"a":1,"b":2})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+ ASSERT_FALSE(fn.current_empty());
+
+ EXPECT_FALSE(fn.eos());
+ fn.forward(1); // offset 0 → 1
+ EXPECT_FALSE(fn.eos());
+ fn.forward(1); // offset 1 → 2, == _cur_size → eos
+ EXPECT_TRUE(fn.eos());
+
+ fn.process_close();
+}
+
+// Non-nullable get_value path (without set_nullable) — struct_col directly,
no ColumnNullable wrapper.
+TEST_F(TableFunctionTest, vjson_each_get_value_non_nullable) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ // Intentionally NOT calling fn.set_nullable()
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ // Non-nullable struct type — no wrapping Nullable
+ DataTypePtr struct_dt = std::make_shared<DataTypeStruct>(DataTypes
{key_dt, val_dt});
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"a":1})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+
+ auto out_col = struct_dt->create_column();
+ int step = fn.get_value(out_col, 10);
+ EXPECT_EQ(1, step);
+ ASSERT_EQ(1U, out_col->size());
+
+ // Directly a ColumnStruct, not wrapped in ColumnNullable
+ const auto& struct_col = assert_cast<const ColumnStruct&>(*out_col);
+ const auto& key_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(0));
+ StringRef k = key_col.get_nested_column().get_data_at(0);
+ EXPECT_EQ("a", std::string(k.data, k.size));
+
+ fn.process_close();
+}
+
+// Non-nullable get_same_many_values path.
+TEST_F(TableFunctionTest, vjson_each_get_same_many_values_non_nullable) {
+ init_expr_context(1);
+ VJsonEachTableFn fn;
+ fn.set_expr_context(_ctx);
+ // NOT calling fn.set_nullable()
+
+ DataTypePtr key_dt =
make_nullable(DataTypeFactory::instance().create_data_type(
+ doris::PrimitiveType::TYPE_VARCHAR, false));
+ DataTypePtr val_dt = make_nullable(
+
DataTypeFactory::instance().create_data_type(doris::PrimitiveType::TYPE_JSONB,
false));
+ DataTypePtr struct_dt = std::make_shared<DataTypeStruct>(DataTypes
{key_dt, val_dt});
+
+ TQueryOptions q_opts;
+ TQueryGlobals q_globals;
+ RuntimeState rs(q_opts, q_globals);
+
+ auto block = build_jsonb_input_block({{R"({"x":1})"}});
+ ASSERT_TRUE(fn.process_init(block.get(), &rs).ok());
+ fn.process_row(0);
+
+ auto out_col = struct_dt->create_column();
+ fn.get_same_many_values(out_col, 2);
+ ASSERT_EQ(2U, out_col->size());
+
+ const auto& struct_col = assert_cast<const ColumnStruct&>(*out_col);
+ const auto& key_col = assert_cast<const
ColumnNullable&>(struct_col.get_column(0));
+ for (size_t i = 0; i < 2; ++i) {
+ StringRef k = key_col.get_nested_column().get_data_at(i);
+ EXPECT_EQ("x", std::string(k.data, k.size));
+ }
+
+ fn.process_close();
+}
+
} // namespace doris
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinTableGeneratingFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinTableGeneratingFunctions.java
index 1ad8b5ccf4b..f764da5718c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinTableGeneratingFunctions.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinTableGeneratingFunctions.java
@@ -38,6 +38,10 @@ import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeOut
import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeSplit;
import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeSplitOuter;
import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeVariantArray;
+import org.apache.doris.nereids.trees.expressions.functions.generator.JsonEach;
+import
org.apache.doris.nereids.trees.expressions.functions.generator.JsonEachOuter;
+import
org.apache.doris.nereids.trees.expressions.functions.generator.JsonEachText;
+import
org.apache.doris.nereids.trees.expressions.functions.generator.JsonEachTextOuter;
import
org.apache.doris.nereids.trees.expressions.functions.generator.PosExplode;
import
org.apache.doris.nereids.trees.expressions.functions.generator.PosExplodeOuter;
import org.apache.doris.nereids.trees.expressions.functions.generator.Unnest;
@@ -78,6 +82,10 @@ public class BuiltinTableGeneratingFunctions implements
FunctionHelper {
tableGenerating(ExplodeJsonArrayJson.class,
"explode_json_array_json"),
tableGenerating(ExplodeJsonArrayJsonOuter.class,
"explode_json_array_json_outer"),
tableGenerating(ExplodeVariantArray.class,
"explode_variant_array"),
+ tableGenerating(JsonEach.class, "json_each"),
+ tableGenerating(JsonEachOuter.class, "json_each_outer"),
+ tableGenerating(JsonEachText.class, "json_each_text"),
+ tableGenerating(JsonEachTextOuter.class, "json_each_text_outer"),
tableGenerating(PosExplode.class, "posexplode"),
tableGenerating(PosExplodeOuter.class, "posexplode_outer"),
tableGenerating(Unnest.class, "unnest")
@@ -89,6 +97,8 @@ public class BuiltinTableGeneratingFunctions implements
FunctionHelper {
.add("explode_json_array_string").add("explode_json_array_json").add("explode_json_array_int_outer")
.add("explode_json_array_double_outer").add("explode_json_array_string_outer")
.add("explode_json_array_json_outer").add("explode_split").add("explode_split_outer")
+ .add("json_each").add("json_each_outer")
+ .add("json_each_text").add("json_each_text_outer")
.add("posexplode").add("posexplode_outer").build();
public Set<String> getReturnManyColumnFunctions() {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEach.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEach.java
new file mode 100644
index 00000000000..acb7a1a0891
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEach.java
@@ -0,0 +1,79 @@
+// 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.generator;
+
+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.literal.StructLiteral;
+import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.JsonType;
+import org.apache.doris.nereids.types.StringType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * json_each(json) expands the top-level JSON object into a set of key/value
+ * pairs.
+ * Returns: Struct(key VARCHAR, value JSON) — one row per top-level key.
+ *
+ * Example:
+ * SELECT key, value FROM LATERAL VIEW json_each('{"a":"foo","b":"bar"}') t AS
+ * key, value
+ * → key="a", value="foo" (JSON-formatted)
+ * → key="b", value="bar"
+ */
+public class JsonEach extends TableGeneratingFunction implements
UnaryExpression, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(StructLiteral.constructStructType(
+ ImmutableList.of(StringType.INSTANCE, JsonType.INSTANCE)))
+ .args(JsonType.INSTANCE));
+
+ /**
+ * Constructor with 1 argument.
+ */
+ public JsonEach(Expression arg) {
+ super("json_each", arg);
+ }
+
+ /** Constructor for withChildren and reuse signature. */
+ private JsonEach(GeneratorFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public JsonEach withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new JsonEach(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitJsonEach(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachOuter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachOuter.java
new file mode 100644
index 00000000000..1c9b2e298d0
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachOuter.java
@@ -0,0 +1,72 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+package org.apache.doris.nereids.trees.expressions.functions.generator;
+
+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.literal.StructLiteral;
+import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.JsonType;
+import org.apache.doris.nereids.types.StringType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * json_each_outer(json) is json_each with outer semantics: emits one NULL row
+ * when the input is NULL or not a JSON object, instead of producing no rows.
+ */
+public class JsonEachOuter extends TableGeneratingFunction implements
UnaryExpression, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(StructLiteral.constructStructType(
+ ImmutableList.of(StringType.INSTANCE, JsonType.INSTANCE)))
+ .args(JsonType.INSTANCE));
+
+ /**
+ * Constructor with 1 argument.
+ */
+ public JsonEachOuter(Expression arg) {
+ super("json_each_outer", arg);
+ }
+
+ /** Constructor for withChildren and reuse signature. */
+ private JsonEachOuter(GeneratorFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public JsonEachOuter withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new JsonEachOuter(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitJsonEachOuter(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachText.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachText.java
new file mode 100644
index 00000000000..21faaf47de3
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachText.java
@@ -0,0 +1,80 @@
+// 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.generator;
+
+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.literal.StructLiteral;
+import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.JsonType;
+import org.apache.doris.nereids.types.StringType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * json_each_text(json) expands the top-level JSON object into a set of
+ * key/value pairs.
+ * Returns: Struct(key VARCHAR, value VARCHAR) — the JSON value is returned as
+ * plain text.
+ *
+ * Example:
+ * SELECT key, value FROM LATERAL VIEW json_each_text('{"a":"foo","b":"bar"}')
t
+ * AS key, value
+ * → key="a", value=foo (plain string, not JSON-quoted)
+ * → key="b", value=bar
+ */
+public class JsonEachText extends TableGeneratingFunction implements
UnaryExpression, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(StructLiteral.constructStructType(
+ ImmutableList.of(StringType.INSTANCE,
StringType.INSTANCE)))
+ .args(JsonType.INSTANCE));
+
+ /**
+ * Constructor with 1 argument.
+ */
+ public JsonEachText(Expression arg) {
+ super("json_each_text", arg);
+ }
+
+ /** Constructor for withChildren and reuse signature. */
+ private JsonEachText(GeneratorFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public JsonEachText withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new JsonEachText(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitJsonEachText(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachTextOuter.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachTextOuter.java
new file mode 100644
index 00000000000..8cc33cf529e
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/generator/JsonEachTextOuter.java
@@ -0,0 +1,73 @@
+// 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.generator;
+
+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.literal.StructLiteral;
+import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.JsonType;
+import org.apache.doris.nereids.types.StringType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * json_each_text_outer(json) is json_each_text with outer semantics: emits one
+ * NULL row when the input is NULL or not a JSON object, instead of producing
no
+ * rows.
+ */
+public class JsonEachTextOuter extends TableGeneratingFunction implements
UnaryExpression, AlwaysNullable {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(StructLiteral.constructStructType(
+ ImmutableList.of(StringType.INSTANCE,
StringType.INSTANCE)))
+ .args(JsonType.INSTANCE));
+
+ /**
+ * Constructor with 1 argument.
+ */
+ public JsonEachTextOuter(Expression arg) {
+ super("json_each_text_outer", arg);
+ }
+
+ /** Constructor for withChildren and reuse signature. */
+ private JsonEachTextOuter(GeneratorFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ @Override
+ public JsonEachTextOuter withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new JsonEachTextOuter(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitJsonEachTextOuter(this, context);
+ }
+}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/TableGeneratingFunctionVisitor.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/TableGeneratingFunctionVisitor.java
index 85af01ebd61..384b3209ee5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/TableGeneratingFunctionVisitor.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/TableGeneratingFunctionVisitor.java
@@ -38,6 +38,10 @@ import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeOut
import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeSplit;
import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeSplitOuter;
import
org.apache.doris.nereids.trees.expressions.functions.generator.ExplodeVariantArray;
+import org.apache.doris.nereids.trees.expressions.functions.generator.JsonEach;
+import
org.apache.doris.nereids.trees.expressions.functions.generator.JsonEachOuter;
+import
org.apache.doris.nereids.trees.expressions.functions.generator.JsonEachText;
+import
org.apache.doris.nereids.trees.expressions.functions.generator.JsonEachTextOuter;
import
org.apache.doris.nereids.trees.expressions.functions.generator.PosExplode;
import
org.apache.doris.nereids.trees.expressions.functions.generator.PosExplodeOuter;
import
org.apache.doris.nereids.trees.expressions.functions.generator.TableGeneratingFunction;
@@ -79,6 +83,22 @@ public interface TableGeneratingFunctionVisitor<R, C> {
return visitTableGeneratingFunction(explodeOuter, context);
}
+ default R visitJsonEach(JsonEach jsonEach, C context) {
+ return visitTableGeneratingFunction(jsonEach, context);
+ }
+
+ default R visitJsonEachOuter(JsonEachOuter jsonEachOuter, C context) {
+ return visitTableGeneratingFunction(jsonEachOuter, context);
+ }
+
+ default R visitJsonEachText(JsonEachText jsonEachText, C context) {
+ return visitTableGeneratingFunction(jsonEachText, context);
+ }
+
+ default R visitJsonEachTextOuter(JsonEachTextOuter jsonEachTextOuter, C
context) {
+ return visitTableGeneratingFunction(jsonEachTextOuter, context);
+ }
+
default R visitExplodeNumbers(ExplodeNumbers explodeNumbers, C context) {
return visitTableGeneratingFunction(explodeNumbers, context);
}
diff --git
a/regression-test/data/query_p0/sql_functions/table_function/json_each.out
b/regression-test/data/query_p0/sql_functions/table_function/json_each.out
new file mode 100644
index 00000000000..fbf7a7d36aa
--- /dev/null
+++ b/regression-test/data/query_p0/sql_functions/table_function/json_each.out
@@ -0,0 +1,174 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !json_each_basic --
+1 a "foo"
+1 b "bar"
+
+-- !json_each_mixed --
+2 x 1
+2 y true
+2 z \N
+
+-- !json_each_empty --
+
+-- !json_each_null_input --
+
+-- !json_each_all --
+1 a "foo"
+1 b "bar"
+2 x 1
+2 y true
+2 z \N
+
+-- !json_each_literal --
+name "doris"
+version 3
+
+-- !json_each_neg_false --
+5 bool_f false
+5 neg -1
+
+-- !json_each_unicode --
+6 cn "中文"
+
+-- !json_each_non_object_str --
+
+-- !json_each_non_object_arr --
+
+-- !json_each_complex --
+9 arr [1,2]
+9 sub {"x":1}
+
+-- !json_each_text_basic --
+1 a foo
+1 b bar
+
+-- !json_each_text_mixed --
+2 x 1
+2 y true
+2 z \N
+
+-- !json_each_text_empty --
+
+-- !json_each_text_null_input --
+
+-- !json_each_text_all --
+1 a foo
+1 b bar
+2 x 1
+2 y true
+2 z \N
+
+-- !json_each_text_literal --
+name doris
+version 3
+
+-- !json_each_text_neg_false --
+5 bool_f false
+5 neg -1
+
+-- !json_each_text_unicode --
+6 cn 中文
+
+-- !json_each_text_non_object_str --
+
+-- !json_each_text_non_object_arr --
+
+-- !json_each_text_complex --
+9 arr [1,2]
+9 sub {"x":1}
+
+-- !json_each_outer_null_input --
+4 \N \N
+
+-- !json_each_outer_empty --
+3 \N \N
+
+-- !json_each_outer_basic --
+1 a "foo"
+1 b "bar"
+
+-- !json_each_outer_non_object --
+7 \N \N
+8 \N \N
+
+-- !json_each_outer_all --
+1 a "foo"
+1 b "bar"
+2 x 1
+2 y true
+2 z \N
+3 \N \N
+4 \N \N
+
+-- !json_each_text_outer_null_input --
+4 \N \N
+
+-- !json_each_text_outer_empty --
+3 \N \N
+
+-- !json_each_text_outer_basic --
+1 a foo
+1 b bar
+
+-- !json_each_text_outer_non_object --
+7 \N \N
+8 \N \N
+
+-- !json_each_text_outer_all --
+1 a foo
+1 b bar
+2 x 1
+2 y true
+2 z \N
+3 \N \N
+4 \N \N
+
+-- !multi_lateral_nested --
+9 sub x 1
+
+-- !multi_lateral_const --
+1 a x 1
+1 a y 2
+1 b x 1
+1 b y 2
+
+-- !multi_lateral_three_mixed --
+1 a a x
+1 a a y
+1 a a z
+1 a b x
+1 a b y
+1 a b z
+1 b a x
+1 b a y
+1 b a z
+1 b b x
+1 b b y
+1 b b z
+
+-- !multi_lateral_cartesian --
+a 1 x 10
+a 1 y 20
+b 2 x 10
+b 2 y 20
+
+-- !corner_deep_nesting --
+level1 {"level2":{"level3":"deep"}}
+
+-- !corner_special_keys --
+key with spaces v1
+key-with-dash v3
+key.with.dots v2
+
+-- !corner_many_keys --
+10
+
+-- !corner_empty_key --
+ empty_key_value
+normal value
+
+-- !corner_const_multi_block --
+const value
+const value
+const value
+
diff --git
a/regression-test/suites/query_p0/sql_functions/table_function/json_each.groovy
b/regression-test/suites/query_p0/sql_functions/table_function/json_each.groovy
new file mode 100644
index 00000000000..b80b4e49524
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/table_function/json_each.groovy
@@ -0,0 +1,419 @@
+// 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('json_each') {
+ sql ''' DROP TABLE IF EXISTS jdata '''
+ sql '''
+ CREATE TABLE IF NOT EXISTS jdata (
+ id INT,
+ jval JSONB
+ ) DUPLICATE KEY(id)
+ DISTRIBUTED BY HASH(id) BUCKETS 1
+ PROPERTIES("replication_num" = "1")
+ '''
+
+ sql """ INSERT INTO jdata VALUES
+ (1, '{"a":"foo","b":"bar"}'),
+ (2, '{"x":1,"y":true,"z":null}'),
+ (3, '{}'),
+ (4, NULL),
+ (5, '{"neg":-1,"bool_f":false}'),
+ (6, '{"cn":"\u4e2d\u6587"}'),
+ (7, '"a_string"'),
+ (8, '[1,2,3]'),
+ (9, '{"arr":[1,2],"sub":{"x":1}}')
+ """
+
+ // ---------- json_each ----------
+
+ // basic string values: value is JSONB, shown with JSON quotes
+ qt_json_each_basic '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 1
+ ORDER BY id, k
+ '''
+
+ // int / bool true / JSON null → SQL NULL
+ qt_json_each_mixed '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 2
+ ORDER BY id, k
+ '''
+
+ // empty object → 0 rows
+ qt_json_each_empty '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 3
+ ORDER BY id, k
+ '''
+
+ // SQL NULL input, non-outer → 0 rows
+ qt_json_each_null_input '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 4
+ ORDER BY id, k
+ '''
+
+ // ids 1-4 combined
+ qt_json_each_all '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id IN (1, 2, 3, 4)
+ ORDER BY id, k
+ '''
+
+ // inline literal
+ qt_json_each_literal """
+ SELECT k, v
+ FROM (SELECT 1) dummy
+ LATERAL VIEW json_each('{"name":"doris","version":3}') t AS k, v
+ ORDER BY k
+ """
+
+ // negative int, boolean false
+ qt_json_each_neg_false '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 5
+ ORDER BY id, k
+ '''
+
+ // unicode string value
+ qt_json_each_unicode '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 6
+ ORDER BY id, k
+ '''
+
+ // non-object input: JSON string → 0 rows
+ qt_json_each_non_object_str '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 7
+ ORDER BY id, k
+ '''
+
+ // non-object input: JSON array → 0 rows
+ qt_json_each_non_object_arr '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 8
+ ORDER BY id, k
+ '''
+
+ // complex value types (nested obj + array): values are JSONB
+ qt_json_each_complex '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each(jval) t AS k, v
+ WHERE id = 9
+ ORDER BY id, k
+ '''
+
+ // ---------- json_each_text ----------
+
+ // string values unquoted in text mode
+ qt_json_each_text_basic '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 1
+ ORDER BY id, k
+ '''
+
+ // int / bool / JSON null → SQL NULL
+ qt_json_each_text_mixed '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 2
+ ORDER BY id, k
+ '''
+
+ // empty object → 0 rows
+ qt_json_each_text_empty '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 3
+ ORDER BY id, k
+ '''
+
+ // SQL NULL input, non-outer → 0 rows
+ qt_json_each_text_null_input '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 4
+ ORDER BY id, k
+ '''
+
+ // ids 1-4 combined
+ qt_json_each_text_all '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id IN (1, 2, 3, 4)
+ ORDER BY id, k
+ '''
+
+ // inline literal: strings unquoted
+ qt_json_each_text_literal """
+ SELECT k, v
+ FROM (SELECT 1) dummy
+ LATERAL VIEW json_each_text('{"name":"doris","version":3}') t AS k, v
+ ORDER BY k
+ """
+
+ // negative int, boolean false
+ qt_json_each_text_neg_false '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 5
+ ORDER BY id, k
+ '''
+
+ // unicode string value: unquoted in text mode
+ qt_json_each_text_unicode '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 6
+ ORDER BY id, k
+ '''
+
+ // non-object input: JSON string → 0 rows
+ qt_json_each_text_non_object_str '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 7
+ ORDER BY id, k
+ '''
+
+ // non-object input: JSON array → 0 rows
+ qt_json_each_text_non_object_arr '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 8
+ ORDER BY id, k
+ '''
+
+ // complex value types in text mode: values are text representation
+ qt_json_each_text_complex '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text(jval) t AS k, v
+ WHERE id = 9
+ ORDER BY id, k
+ '''
+
+ // ---------- json_each_outer ----------
+
+ // outer: NULL input → 1 row with NULL k, NULL v (original row preserved)
+ qt_json_each_outer_null_input '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_outer(jval) t AS k, v
+ WHERE id = 4
+ ORDER BY id, k
+ '''
+
+ // outer: empty object → 1 row with NULL k, NULL v
+ qt_json_each_outer_empty '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_outer(jval) t AS k, v
+ WHERE id = 3
+ ORDER BY id, k
+ '''
+
+ // outer: normal object → same result as non-outer json_each
+ qt_json_each_outer_basic '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_outer(jval) t AS k, v
+ WHERE id = 1
+ ORDER BY id, k
+ '''
+
+ // outer: non-object inputs (string / array) → 1 row each with NULL k,
NULL v
+ qt_json_each_outer_non_object '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_outer(jval) t AS k, v
+ WHERE id IN (7, 8)
+ ORDER BY id, k
+ '''
+
+ // outer: mixed ids 1-4: id=3 (empty) and id=4 (NULL) each emit one
NULL-padded row
+ qt_json_each_outer_all '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_outer(jval) t AS k, v
+ WHERE id IN (1, 2, 3, 4)
+ ORDER BY id, k
+ '''
+
+ // ---------- json_each_text_outer ----------
+
+ // outer: NULL input → 1 row with NULL k, NULL v
+ qt_json_each_text_outer_null_input '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text_outer(jval) t AS k, v
+ WHERE id = 4
+ ORDER BY id, k
+ '''
+
+ // outer: empty object → 1 row with NULL k, NULL v
+ qt_json_each_text_outer_empty '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text_outer(jval) t AS k, v
+ WHERE id = 3
+ ORDER BY id, k
+ '''
+
+ // outer: normal object → same as json_each_text (strings unquoted)
+ qt_json_each_text_outer_basic '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text_outer(jval) t AS k, v
+ WHERE id = 1
+ ORDER BY id, k
+ '''
+
+ // outer: non-object inputs → 1 row each with NULL k, NULL v
+ qt_json_each_text_outer_non_object '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text_outer(jval) t AS k, v
+ WHERE id IN (7, 8)
+ ORDER BY id, k
+ '''
+
+ // outer: mixed ids 1-4
+ qt_json_each_text_outer_all '''
+ SELECT id, k, v
+ FROM jdata
+ LATERAL VIEW json_each_text_outer(jval) t AS k, v
+ WHERE id IN (1, 2, 3, 4)
+ ORDER BY id, k
+ '''
+
+ // ---------- Multiple LATERAL VIEW combinations ----------
+
+ // double json_each: expand nested object
+ qt_multi_lateral_nested '''
+ SELECT id, k1, k2, v2
+ FROM jdata
+ LATERAL VIEW json_each(jval) t1 AS k1, v1
+ LATERAL VIEW json_each(v1) t2 AS k2, v2
+ WHERE id = 9 AND k1 = 'sub'
+ ORDER BY k1, k2
+ '''
+
+ // json_each with const literal in second lateral view
+ qt_multi_lateral_const '''
+ SELECT id, k1, k2, v2
+ FROM jdata
+ LATERAL VIEW json_each(jval) t1 AS k1, v1
+ LATERAL VIEW json_each('{"x":1,"y":2}') t2 AS k2, v2
+ WHERE id = 1
+ ORDER BY k1, k2
+ '''
+
+ // three lateral views: column + two const literals with different sizes
+ qt_multi_lateral_three_mixed '''
+ SELECT id, k1, k2, k3
+ FROM jdata
+ LATERAL VIEW json_each(jval) t1 AS k1, v1
+ LATERAL VIEW json_each('{"a":1,"b":2}') t2 AS k2, v2
+ LATERAL VIEW json_each('{"x":1,"y":2,"z":3}') t3 AS k3, v3
+ WHERE id = 1
+ ORDER BY k1, k2, k3
+ '''
+
+ // multiple json_each on same row: cartesian product
+ qt_multi_lateral_cartesian '''
+ SELECT k1, v1, k2, v2
+ FROM (SELECT '{"a":1,"b":2}' AS j1, '{"x":10,"y":20}' AS j2) t
+ LATERAL VIEW json_each(j1) t1 AS k1, v1
+ LATERAL VIEW json_each(j2) t2 AS k2, v2
+ ORDER BY k1, k2
+ '''
+
+ // ---------- Corner cases ----------
+
+ // deeply nested object keys
+ qt_corner_deep_nesting '''
+ SELECT k, v
+ FROM (SELECT '{"level1":{"level2":{"level3":"deep"}}}' AS j) t
+ LATERAL VIEW json_each(j) t AS k, v
+ ORDER BY k
+ '''
+
+ // special characters in keys
+ qt_corner_special_keys '''
+ SELECT k, v
+ FROM (SELECT '{"key with
spaces":"v1","key.with.dots":"v2","key-with-dash":"v3"}' AS j) t
+ LATERAL VIEW json_each_text(j) t AS k, v
+ ORDER BY k
+ '''
+
+ // large number of keys
+ qt_corner_many_keys '''
+ SELECT COUNT(*) AS key_count
+ FROM (SELECT
'{"k1":1,"k2":2,"k3":3,"k4":4,"k5":5,"k6":6,"k7":7,"k8":8,"k9":9,"k10":10}' AS
j) t
+ LATERAL VIEW json_each(j) t AS k, v
+ '''
+
+ // empty string key
+ qt_corner_empty_key '''
+ SELECT k, v
+ FROM (SELECT '{"":"empty_key_value","normal":"value"}' AS j) t
+ LATERAL VIEW json_each_text(j) t AS k, v
+ ORDER BY k
+ '''
+
+ // const input across multiple blocks: test process_close state reset
+ qt_corner_const_multi_block '''
+ SELECT k, v
+ FROM (
+ SELECT 1 AS id UNION ALL SELECT 2 UNION ALL SELECT 3
+ ) t
+ LATERAL VIEW json_each_text('{"const":"value"}') t AS k, v
+ ORDER BY id, k
+ '''
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
