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.
explode_map(expr) explode_map_outer(expr)
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 | +----------+---------+------+---------+------+
EXPLODE_MAP,EXPLODE_MAP_OUTER,MAP