跳到主要内容
跳到主要内容

EXPLODE_MAP

explode

description

Table functions must be used in conjunction with Lateral View, support multi conjunction with Lateral View,support new optimizer only.

explode map column to rows. explode_map_outer will return NULL, while map is NULL or empty. explode_map and explode_map_outer both keep the nested NULL elements of map.

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