morrySnow commented on code in PR #1831:
URL: https://github.com/apache/doris-website/pull/1831#discussion_r1919501817
##########
docs/sql-manual/sql-functions/table-functions/explode-json-object-outer.md:
##########
@@ -26,72 +26,79 @@ 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.
+`explode_json_object` expands a JSON object into multiple rows, with each row
containing a key-value pair. It is typically used to process JSON data and
expand the JSON object into a more queryable format. This function only
supports non-empty JSON objects.
-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.
+`explode_json_object_outer` is similar to `explode_json_object`, but with
different behavior when handling empty and NULL values. It can retain empty or
NULL JSON objects and return corresponding records.
## Syntax
```sql
-posexplode(array)
-posexplode_outer(array)
+explode_json_object(<expr>)
+explode_json_object_outer(<expr>)
```
-### Example
+## Parameters
+
+| Parameter | Description |
+| -- | -- |
+| `<json>` | json type |
+
+## Return Value
+
+When the JSON object is neither empty nor NULL, the return values of
`explode_json_object` and `explode_json_object_outer` are the same. Each
key-value pair generates one row, with the key as one column and the value as
another column.
+
+When the JSON object is empty or NULL:
+
+`explode_json_object` will not return any rows.
+`explode_json_object_outer` will return one row, with the expanded columns
being NULL.
+
+## Examples
```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 |
-+------+----------+--------------------------------+------+---------+
+CREATE TABLE example (
+ id INT,
+ value_json json
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
+PROPERTIES (
+"replication_allocation" = "tag.location.default: 1");
+
+INSERT INTO example VALUES
+(1, '{"key1": "value1", "key2": "value2"}'),
+(2, '{}'),
+(3, NULL);
+
+mysql> select * from example;
++------+-----------------------------------+
+| id | value_json |
++------+-----------------------------------+
+| 2 | {} |
+| 1 | {"key1":"value1","key2":"value2"} |
+| 3 | NULL |
++------+-----------------------------------+
+
+mysql> SELECT id, k, v
+ -> FROM example
+ -> LATERAL VIEW explode_json_object(value_json) exploded_table AS k , v;
++------+------+----------+
+| id | k | v |
++------+------+----------+
+| 1 | key1 | "value1" |
+| 1 | key2 | "value2" |
++------+------+----------+
+
+mysql> SELECT id, k, v
+ -> FROM example
+ -> LATERAL VIEW explode_json_object_outer(value_json) exploded_table AS k,
v;
++------+------+----------+
+| id | k | v |
++------+------+----------+
+| 3 | NULL | NULL |
+| 1 | key1 | "value1" |
+| 1 | key2 | "value2" |
+| 2 | NULL | NULL |
++------+------+----------+
+
```
### Keywords
-POSEXPLODE,POSEXPLODE_OUTER
+EXPLODE_JSON_OBJECT,EXPLODE_JSON_OBJECT_OUTER,JSON
Review Comment:
去掉keywords 章节
##########
docs/sql-manual/sql-functions/table-functions/explode-json-object-outer.md:
##########
@@ -26,72 +26,79 @@ 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.
+`explode_json_object` expands a JSON object into multiple rows, with each row
containing a key-value pair. It is typically used to process JSON data and
expand the JSON object into a more queryable format. This function only
supports non-empty JSON objects.
-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.
+`explode_json_object_outer` is similar to `explode_json_object`, but with
different behavior when handling empty and NULL values. It can retain empty or
NULL JSON objects and return corresponding records.
## Syntax
```sql
-posexplode(array)
-posexplode_outer(array)
+explode_json_object(<expr>)
+explode_json_object_outer(<expr>)
```
-### Example
+## Parameters
+
+| Parameter | Description |
+| -- | -- |
+| `<json>` | json type |
Review Comment:
语法里面是<expr> 这里是 <json>
两个要保持一致
##########
docs/sql-manual/sql-functions/table-functions/explode-json-object-outer.md:
##########
@@ -26,72 +26,79 @@ 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.
+`explode_json_object` expands a JSON object into multiple rows, with each row
containing a key-value pair. It is typically used to process JSON data and
expand the JSON object into a more queryable format. This function only
supports non-empty JSON objects.
-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.
+`explode_json_object_outer` is similar to `explode_json_object`, but with
different behavior when handling empty and NULL values. It can retain empty or
NULL JSON objects and return corresponding records.
## Syntax
```sql
-posexplode(array)
-posexplode_outer(array)
+explode_json_object(<expr>)
+explode_json_object_outer(<expr>)
```
-### Example
+## Parameters
+
+| Parameter | Description |
+| -- | -- |
+| `<json>` | json type |
+
+## Return Value
+
+When the JSON object is neither empty nor NULL, the return values of
`explode_json_object` and `explode_json_object_outer` are the same. Each
key-value pair generates one row, with the key as one column and the value as
another column.
+
+When the JSON object is empty or NULL:
+
+`explode_json_object` will not return any rows.
+`explode_json_object_outer` will return one row, with the expanded columns
being NULL.
+
+## Examples
```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 |
-+------+----------+--------------------------------+------+---------+
+CREATE TABLE example (
+ id INT,
+ value_json json
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
+PROPERTIES (
+"replication_allocation" = "tag.location.default: 1");
+
+INSERT INTO example VALUES
+(1, '{"key1": "value1", "key2": "value2"}'),
+(2, '{}'),
+(3, NULL);
+
+mysql> select * from example;
++------+-----------------------------------+
+| id | value_json |
++------+-----------------------------------+
+| 2 | {} |
+| 1 | {"key1":"value1","key2":"value2"} |
+| 3 | NULL |
++------+-----------------------------------+
+
+mysql> SELECT id, k, v
+ -> FROM example
+ -> LATERAL VIEW explode_json_object(value_json) exploded_table AS k , v;
++------+------+----------+
+| id | k | v |
++------+------+----------+
+| 1 | key1 | "value1" |
+| 1 | key2 | "value2" |
++------+------+----------+
+
+mysql> SELECT id, k, v
+ -> FROM example
+ -> LATERAL VIEW explode_json_object_outer(value_json) exploded_table AS k,
v;
++------+------+----------+
Review Comment:
结果和查询要分到两个code block中
##########
docs/sql-manual/sql-functions/table-functions/explode-json-object-outer.md:
##########
@@ -26,72 +26,79 @@ 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.
+`explode_json_object` expands a JSON object into multiple rows, with each row
containing a key-value pair. It is typically used to process JSON data and
expand the JSON object into a more queryable format. This function only
supports non-empty JSON objects.
-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.
+`explode_json_object_outer` is similar to `explode_json_object`, but with
different behavior when handling empty and NULL values. It can retain empty or
NULL JSON objects and return corresponding records.
## Syntax
```sql
-posexplode(array)
-posexplode_outer(array)
+explode_json_object(<expr>)
+explode_json_object_outer(<expr>)
```
-### Example
+## Parameters
+
+| Parameter | Description |
+| -- | -- |
+| `<json>` | json type |
+
+## Return Value
+
+When the JSON object is neither empty nor NULL, the return values of
`explode_json_object` and `explode_json_object_outer` are the same. Each
key-value pair generates one row, with the key as one column and the value as
another column.
+
+When the JSON object is empty or NULL:
+
+`explode_json_object` will not return any rows.
+`explode_json_object_outer` will return one row, with the expanded columns
being NULL.
+
+## Examples
```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 |
-+------+----------+--------------------------------+------+---------+
+CREATE TABLE example (
+ id INT,
+ value_json json
+) DUPLICATE KEY(id)
+DISTRIBUTED BY HASH(`id`) BUCKETS AUTO
+PROPERTIES (
+"replication_allocation" = "tag.location.default: 1");
+
+INSERT INTO example VALUES
+(1, '{"key1": "value1", "key2": "value2"}'),
+(2, '{}'),
+(3, NULL);
+
+mysql> select * from example;
Review Comment:
去掉prompt
```suggestion
select * from example;
```
##########
docs/sql-manual/sql-functions/table-functions/explode-map-outer.md:
##########
@@ -26,72 +26,119 @@ 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.
+The `explode_map_outer` function takes a map (mapping type) and expands it
into multiple rows, with each row containing a key-value pair. It is typically
used in conjunction with LATERAL VIEW and can support multiple lateral views.
It is supported only by 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.
+The main difference between `explode_map` and `explode_map_outer` lies in the
handling of null values.
## Syntax
+
```sql
-posexplode(array)
-posexplode_outer(array)
+explode_map(<expr>)
+explode_map_outer(<expr>)
```
-### Example
+## Parameters
-```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 |
-+------+----------+--------------------------------+------+---------+
+| Parameter | Description |
+| -- | -- |
+| `map<k,v>` | map type |
+
+## Return Value
+
+When the map is not empty or NULL, the return values of `explode_map` and
`explode_map_outer` are the same.
+
+When the data is empty or NULL:
+
+`explode_map` Only processes non-empty map types. If the map is empty or NULL,
`explode_map` will not return any rows.
+`explode_map_outer` If the map is empty or NULL, explode_map_outer will retain
the record with the empty or NULL map and return a row with NULL values.
+
+## Examples
+```
Review Comment:
sql部分需要使用
````
```sql
````
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
