EXPLODE_MAP
更新时间：2025-10-16
描述
explode_mpa 函数接受一个 map (映射类型)，将 map（映射类型）展开成多个行，每行包含一个键值对。通常与 LATERAL VIEW 配合使用，可以支持多个 Lateral view。仅支持新优化器。
explode_map 和 explode_map_outer 区别主要在于空值处理。
语法
SQL
1EXPLODE_MAP(map<k,v>)
2EXPLODE_MAP_OUTER(map<k,v>)参数
| 参数 | 说明 |
|---|---|
map<k,v> |
map 类型 |
返回值
当 map 不为空或 NULL 时，explode_map 和 explode_map_outer 的返回值相同。
当数据为空或 NULL 时：
explode_map 只处理包含元素的 map。如果 map 是空的或为 NULL，explode_map 不会返回任何行。
explode_map_outer 如果 map 是空的或为 NULL，会保留空 map 或 NULL 的记录，返回的行将包含 NULL 值。
举例
SQL
1SET enable_nereids_planner=true
SQL
1SET enable_fallback_to_original_planner=false
SQL
1CREATE TABLE IF NOT EXISTS `sdu`(
2 `id` INT NULL,
3 `name` TEXT NULL,
4 `score` MAP<TEXT,INT> NULL
5 ) ENGINE=OLAP
6 DUPLICATE KEY(`id`)
7 COMMENT 'OLAP'
8 DISTRIBUTED BY HASH(`id`) BUCKETS 1
9 PROPERTIES ("replication_allocation" = "tag.location.default: 1");
10Query OK, 0 rows affected (0.15 sec)
SQL
1insert into sdu values (0, "zhangsan", {"Chinese":"80","Math":"60","English":"90"}), (1, "lisi", {"null":null}), (2, "wangwu", {"Chinese":"88","Math":"90","English":"96"}), (3, "lisi2", {null:null}), (4, "amory", NULL);
2Query OK, 5 rows affected (0.23 sec)
3{'label':'label_9b35d9d9d59147f5_bffb974881ed2133', 'status':'VISIBLE', 'txnId':'4005'}
SQL
1select * from sdu order by id;
Text
1+------+----------+-----------------------------------------+
2| id | name | score |
3+------+----------+-----------------------------------------+
4| 0 | zhangsan | {"Chinese":80, "Math":60, "English":90} |
5| 1 | lisi | {"null":null} |
6| 2 | wangwu | {"Chinese":88, "Math":90, "English":96} |
7| 3 | lisi2 | {null:null} |
8| 4 | amory | NULL |
9+------+----------+-----------------------------------------+
SQL
1select name, k,v from sdu lateral view explode_map(score) tmp as k,v;
Text
1+----------+---------+------+
2| name | k | v |
3+----------+---------+------+
4| zhangsan | Chinese | 80 |
5| zhangsan | Math | 60 |
6| zhangsan | English | 90 |
7| lisi | null | NULL |
8| wangwu | Chinese | 88 |
9| wangwu | Math | 90 |
10| wangwu | English | 96 |
11| lisi2 | NULL | NULL |
12+----------+---------+------+
SQL
1select name, k,v from sdu lateral view explode_map_outer(score) tmp as k,v;
Text
1+----------+---------+------+
2| name | k | v |
3+----------+---------+------+
4| zhangsan | Chinese | 80 |
5| zhangsan | Math | 60 |
6| zhangsan | English | 90 |
7| lisi | null | NULL |
8| wangwu | Chinese | 88 |
9| wangwu | Math | 90 |
10| wangwu | English | 96 |
11| lisi2 | NULL | NULL |
12| amory | NULL | NULL |
13+----------+---------+------+
SQL
1select name, k,v,k1,v1 from sdu lateral view explode_map_outer(score) tmp as k,v lateral view explode_map(score) tmp2 as k1,v1;
Text
1+----------+---------+------+---------+------+
2| name | k | v | k1 | v1 |
3+----------+---------+------+---------+------+
4| zhangsan | Chinese | 80 | Chinese | 80 |
5| zhangsan | Chinese | 80 | Math | 60 |
6| zhangsan | Chinese | 80 | English | 90 |
7| zhangsan | Math | 60 | Chinese | 80 |
8| zhangsan | Math | 60 | Math | 60 |
9| zhangsan | Math | 60 | English | 90 |
10| zhangsan | English | 90 | Chinese | 80 |
11| zhangsan | English | 90 | Math | 60 |
12| zhangsan | English | 90 | English | 90 |
13| lisi | null | NULL | null | NULL |
14| wangwu | Chinese | 88 | Chinese | 88 |
15| wangwu | Chinese | 88 | Math | 90 |
16| wangwu | Chinese | 88 | English | 96 |
17| wangwu | Math | 90 | Chinese | 88 |
18| wangwu | Math | 90 | Math | 90 |
19| wangwu | Math | 90 | English | 96 |
20| wangwu | English | 96 | Chinese | 88 |
21| wangwu | English | 96 | Math | 90 |
22| wangwu | English | 96 | English | 96 |
23| lisi2 | NULL | NULL | NULL | NULL |
24+----------+---------+------+---------+------+