explode_map
description
表函数，需配合 Lateral View 使用, 可以支持多个 Lateral view, 仅仅支持新优化器。
将 map 列展开成多行。当 map 为NULL或者为空时，explode_map_outer 返回NULL。
explode_map 和 explode_map_outer 均会返回 map 内部的NULL元素。
syntax
explode_map(expr)
explode_map_outer(expr)example
mysql> SET enable_fallback_to_original_planner=false
mysql> CREATE TABLE IF NOT EXISTS `sdu`(
`id` INT NULL,
`name` TEXT NULL,
`score` MAP<TEXT,INT> NULL
) ENGINE=OLAP
DUPLICATE KEY(`id`)
COMMENT 'OLAP'
DISTRIBUTED BY HASH(`id`) BUCKETS 1
PROPERTIES ("replication_allocation" = "tag.location.default: 1");
Query OK, 0 rows affected (0.15 sec)
mysql> insert 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);
Query OK, 5 rows affected (0.23 sec)
{'label':'label_9b35d9d9d59147f5_bffb974881ed2133', 'status':'VISIBLE', 'txnId':'4005'}
mysql> select * from sdu order by id;
+------+----------+-----------------------------------------+
| id | name | score |
+------+----------+-----------------------------------------+
| 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 |
+------+----------+-----------------------------------------+
mysql> select name, k,v from sdu lateral view explode_map(score) tmp as k,v;
+----------+---------+------+
| name | k | v |
+----------+---------+------+
| zhangsan | Chinese | 80 |
| zhangsan | Math | 60 |
| zhangsan | English | 90 |
| lisi | null | NULL |
| wangwu | Chinese | 88 |
| wangwu | Math | 90 |
| wangwu | English | 96 |
| lisi2 | NULL | NULL |
+----------+---------+------+
mysql> select name, k,v from sdu lateral view explode_map_outer(score) tmp as k,v;
+----------+---------+------+
| name | k | v |
+----------+---------+------+
| zhangsan | Chinese | 80 |
| zhangsan | Math | 60 |
| zhangsan | English | 90 |
| lisi | null | NULL |
| wangwu | Chinese | 88 |
| wangwu | Math | 90 |
| wangwu | English | 96 |
| lisi2 | NULL | NULL |
| amory | NULL | NULL |
+----------+---------+------+
mysql> select 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;
+----------+---------+------+---------+------+
| name | k | v | k1 | v1 |
+----------+---------+------+---------+------+
| zhangsan | Chinese | 80 | Chinese | 80 |
| zhangsan | Chinese | 80 | Math | 60 |
| zhangsan | Chinese | 80 | English | 90 |
| zhangsan | Math | 60 | Chinese | 80 |
| zhangsan | Math | 60 | Math | 60 |
| zhangsan | Math | 60 | English | 90 |
| zhangsan | English | 90 | Chinese | 80 |
| zhangsan | English | 90 | Math | 60 |
| zhangsan | English | 90 | English | 90 |
| lisi | null | NULL | null | NULL |
| wangwu | Chinese | 88 | Chinese | 88 |
| wangwu | Chinese | 88 | Math | 90 |
| wangwu | Chinese | 88 | English | 96 |
| wangwu | Math | 90 | Chinese | 88 |
| wangwu | Math | 90 | Math | 90 |
| wangwu | Math | 90 | English | 96 |
| wangwu | English | 96 | Chinese | 88 |
| wangwu | English | 96 | Math | 90 |
| wangwu | English | 96 | English | 96 |
| lisi2 | NULL | NULL | NULL | NULL |
+----------+---------+------+---------+------+keywords
EXPLODE_MAP,EXPLODE_MAP_OUTER,MAP