This is an automated email from the ASF dual-hosted git repository.
zhangstar333 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 8abd63d9a80 [doc](function) support table-function posexplode (#1283)
8abd63d9a80 is described below
commit 8abd63d9a800a00654e2db7b5eb34d0e214bec80
Author: zhangstar333 <[email protected]>
AuthorDate: Thu Nov 21 16:26:54 2024 +0800
[doc](function) support table-function posexplode (#1283)
# Versions
- [x] dev
- [ ] 3.0
- [ ] 2.1
- [ ] 2.0
# Languages
- [x] Chinese
- [x] English
---
.../sql-functions/table-functions/posexplode.md | 97 +++++++++++++++++++++
.../sql-functions/table-functions/posexplode.md | 99 ++++++++++++++++++++++
sidebars.json | 1 +
3 files changed, 197 insertions(+)
diff --git a/docs/sql-manual/sql-functions/table-functions/posexplode.md
b/docs/sql-manual/sql-functions/table-functions/posexplode.md
new file mode 100644
index 00000000000..dcc6955223f
--- /dev/null
+++ b/docs/sql-manual/sql-functions/table-functions/posexplode.md
@@ -0,0 +1,97 @@
+---
+{
+"title": "POSEXPLODE",
+"language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## Description
+
+The table function is used in conjunction with Lateral View and can support
multiple Lateral Views. It only supports the new optimizer.
+
+It expands an array column into multiple rows and adds a column indicating the
position, returning a struct type. When the array is NULL or empty,
posexplode_outer returns NULL. Both posexplode and posexplode_outer will return
NULL elements within the array.
+
+## Syntax
+```sql
+posexplode(array)
+posexplode_outer(array)
+```
+
+### Example
+
+```sql
+ CREATE TABLE IF NOT EXISTS `table_test`(
+ `id` INT NULL,
+ `name` TEXT NULL,
+ `score` array<string> NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES ("replication_allocation" = "tag.location.default: 1");
+
+mysql> insert into table_test values (0, "zhangsan",
["Chinese","Math","English"]),(1, "lisi", ["null"]),(2, "wangwu",
["88a","90b","96c"]),(3, "lisi2", [null]),(4, "amory", NULL);
+
+
+mysql [test_query_qa]>select * from table_test order by id;
++------+----------+--------------------------------+
+| id | name | score |
++------+----------+--------------------------------+
+| 0 | zhangsan | ["Chinese", "Math", "English"] |
+| 1 | lisi | ["null"] |
+| 2 | wangwu | ["88a", "90b", "96c"] |
+| 3 | lisi2 | [null] |
+| 4 | amory | NULL |
++------+----------+--------------------------------+
+
+mysql [test_query_qa]>select id,name,score, k,v from table_test lateral view
posexplode(score) tmp as k,v order by id;
++------+----------+--------------------------------+------+---------+
+| id | name | score | k | v |
++------+----------+--------------------------------+------+---------+
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 0 | Chinese |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 1 | Math |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 2 | English |
+| 1 | lisi | ["null"] | 0 | null |
+| 2 | wangwu | ["88a", "90b", "96c"] | 0 | 88a |
+| 2 | wangwu | ["88a", "90b", "96c"] | 1 | 90b |
+| 2 | wangwu | ["88a", "90b", "96c"] | 2 | 96c |
+| 3 | lisi2 | [null] | 0 | NULL |
++------+----------+--------------------------------+------+---------+
+
+mysql [test_query_qa]>select id,name,score, k,v from table_test lateral view
posexplode_outer(score) tmp as k,v order by id;
++------+----------+--------------------------------+------+---------+
+| id | name | score | k | v |
++------+----------+--------------------------------+------+---------+
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 0 | Chinese |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 1 | Math |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 2 | English |
+| 1 | lisi | ["null"] | 0 | null |
+| 2 | wangwu | ["88a", "90b", "96c"] | 0 | 88a |
+| 2 | wangwu | ["88a", "90b", "96c"] | 1 | 90b |
+| 2 | wangwu | ["88a", "90b", "96c"] | 2 | 96c |
+| 3 | lisi2 | [null] | 0 | NULL |
+| 4 | amory | NULL | NULL | NULL |
++------+----------+--------------------------------+------+---------+
+```
+
+### Keywords
+POSEXPLODE,POSEXPLODE_OUTER
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/table-functions/posexplode.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/table-functions/posexplode.md
new file mode 100644
index 00000000000..0b8001a7fca
--- /dev/null
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-functions/table-functions/posexplode.md
@@ -0,0 +1,99 @@
+---
+{
+"title": "POSEXPLODE",
+"language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+## Description
+
+表函数，需配合 Lateral View 使用, 可以支持多个 Lateral view, 仅仅支持新优化器。
+
+将 array 列展开成多行, 并且增加一列标明位置的列，组成struct类型返回。
+当 array 为NULL或者为空时，`posexplode_outer` 返回NULL。
+`posexplode` 和 `posexplode_outer` 均会返回 array 内部的NULL元素。
+
+## Syntax
+```sql
+posexplode(expr)
+posexplode_outer(expr)
+```
+
+### Example
+
+``` sql
+ CREATE TABLE IF NOT EXISTS `table_test`(
+ `id` INT NULL,
+ `name` TEXT NULL,
+ `score` array<string> NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`id`) BUCKETS 1
+ PROPERTIES ("replication_allocation" = "tag.location.default: 1");
+
+mysql> insert into table_test values (0, "zhangsan",
["Chinese","Math","English"]),(1, "lisi", ["null"]),(2, "wangwu",
["88a","90b","96c"]),(3, "lisi2", [null]),(4, "amory", NULL);
+
+
+mysql [test_query_qa]>select * from table_test order by id;
++------+----------+--------------------------------+
+| id | name | score |
++------+----------+--------------------------------+
+| 0 | zhangsan | ["Chinese", "Math", "English"] |
+| 1 | lisi | ["null"] |
+| 2 | wangwu | ["88a", "90b", "96c"] |
+| 3 | lisi2 | [null] |
+| 4 | amory | NULL |
++------+----------+--------------------------------+
+
+mysql [test_query_qa]>select id,name,score, k,v from table_test lateral view
posexplode(score) tmp as k,v order by id;
++------+----------+--------------------------------+------+---------+
+| id | name | score | k | v |
++------+----------+--------------------------------+------+---------+
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 0 | Chinese |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 1 | Math |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 2 | English |
+| 1 | lisi | ["null"] | 0 | null |
+| 2 | wangwu | ["88a", "90b", "96c"] | 0 | 88a |
+| 2 | wangwu | ["88a", "90b", "96c"] | 1 | 90b |
+| 2 | wangwu | ["88a", "90b", "96c"] | 2 | 96c |
+| 3 | lisi2 | [null] | 0 | NULL |
++------+----------+--------------------------------+------+---------+
+
+mysql [test_query_qa]>select id,name,score, k,v from table_test lateral view
posexplode_outer(score) tmp as k,v order by id;
++------+----------+--------------------------------+------+---------+
+| id | name | score | k | v |
++------+----------+--------------------------------+------+---------+
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 0 | Chinese |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 1 | Math |
+| 0 | zhangsan | ["Chinese", "Math", "English"] | 2 | English |
+| 1 | lisi | ["null"] | 0 | null |
+| 2 | wangwu | ["88a", "90b", "96c"] | 0 | 88a |
+| 2 | wangwu | ["88a", "90b", "96c"] | 1 | 90b |
+| 2 | wangwu | ["88a", "90b", "96c"] | 2 | 96c |
+| 3 | lisi2 | [null] | 0 | NULL |
+| 4 | amory | NULL | NULL | NULL |
++------+----------+--------------------------------+------+---------+
+```
+
+### Keywords
+POSEXPLODE,POSEXPLODE_OUTER
diff --git a/sidebars.json b/sidebars.json
index 8983eea9c9d..391a71d490f 100644
--- a/sidebars.json
+++ b/sidebars.json
@@ -1255,6 +1255,7 @@
"items": [
"sql-manual/sql-functions/table-functions/explode-json-array",
"sql-manual/sql-functions/table-functions/explode",
+
"sql-manual/sql-functions/table-functions/posexplode",
"sql-manual/sql-functions/table-functions/explode-split",
"sql-manual/sql-functions/table-functions/explode_map",
"sql-manual/sql-functions/table-functions/explode-bitmap",
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
