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

  1. explode_map(expr)
  2. explode_map_outer(expr)

example

  1. mysql> set enable_vectorized_engine = true
  2. mysql> SET enable_nereids_planner=true
  3. mysql> SET enable_fallback_to_original_planner=false
  5. mysql> CREATE TABLE IF NOT EXISTS `sdu`(
  6.                    `id` INT NULL,
  7.                    `name` TEXT NULL,
  8.                    `score` MAP<TEXT,INT> NULL
  9.                  ) ENGINE=OLAP
  10.                  DUPLICATE KEY(`id`)
  11.                  COMMENT 'OLAP'
  12.                  DISTRIBUTED BY HASH(`id`) BUCKETS 1
  13.                  PROPERTIES ("replication_allocation" = "tag.location.default: 1");
  14. Query OK, 0 rows affected (0.15 sec)
  16. 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);
  17. Query OK, 5 rows affected (0.23 sec)
  18. {'label':'label_9b35d9d9d59147f5_bffb974881ed2133', 'status':'VISIBLE', 'txnId':'4005'}
  20. mysql> select * from sdu order by id;
  21. +------+----------+-----------------------------------------+
  22. | id   | name     | score                                   |
  23. +------+----------+-----------------------------------------+
  24. |    0 | zhangsan | {"Chinese":80, "Math":60, "English":90} |
  25. |    1 | lisi     | {"null":null}                           |
  26. |    2 | wangwu   | {"Chinese":88, "Math":90, "English":96} |
  27. |    3 | lisi2    | {null:null}                             |
  28. |    4 | amory    | NULL                                    |
  29. +------+----------+-----------------------------------------+
  31. mysql> select name, k,v from sdu lateral view explode_map(score) tmp as k,v;
  32. +----------+---------+------+
  33. | name     | k       | v    |
  34. +----------+---------+------+
  35. | zhangsan | Chinese |   80 |
  36. | zhangsan | Math    |   60 |
  37. | zhangsan | English |   90 |
  38. | lisi     | null    | NULL |
  39. | wangwu   | Chinese |   88 |
  40. | wangwu   | Math    |   90 |
  41. | wangwu   | English |   96 |
  42. | lisi2    | NULL    | NULL |
  43. +----------+---------+------+
  45. mysql> select name, k,v from sdu lateral view explode_map_outer(score) tmp as k,v;
  46. +----------+---------+------+
  47. | name     | k       | v    |
  48. +----------+---------+------+
  49. | zhangsan | Chinese |   80 |
  50. | zhangsan | Math    |   60 |
  51. | zhangsan | English |   90 |
  52. | lisi     | null    | NULL |
  53. | wangwu   | Chinese |   88 |
  54. | wangwu   | Math    |   90 |
  55. | wangwu   | English |   96 |
  56. | lisi2    | NULL    | NULL |
  57. | amory    | NULL    | NULL |
  58. +----------+---------+------+
  60. 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;
  61. +----------+---------+------+---------+------+
  62. | name     | k       | v    | k1      | v1   |
  63. +----------+---------+------+---------+------+
  64. | zhangsan | Chinese |   80 | Chinese |   80 |
  65. | zhangsan | Chinese |   80 | Math    |   60 |
  66. | zhangsan | Chinese |   80 | English |   90 |
  67. | zhangsan | Math    |   60 | Chinese |   80 |
  68. | zhangsan | Math    |   60 | Math    |   60 |
  69. | zhangsan | Math    |   60 | English |   90 |
  70. | zhangsan | English |   90 | Chinese |   80 |
  71. | zhangsan | English |   90 | Math    |   60 |
  72. | zhangsan | English |   90 | English |   90 |
  73. | lisi     | null    | NULL | null    | NULL |
  74. | wangwu   | Chinese |   88 | Chinese |   88 |
  75. | wangwu   | Chinese |   88 | Math    |   90 |
  76. | wangwu   | Chinese |   88 | English |   96 |
  77. | wangwu   | Math    |   90 | Chinese |   88 |
  78. | wangwu   | Math    |   90 | Math    |   90 |
  79. | wangwu   | Math    |   90 | English |   96 |
  80. | wangwu   | English |   96 | Chinese |   88 |
  81. | wangwu   | English |   96 | Math    |   90 |
  82. | wangwu   | English |   96 | English |   96 |
  83. | lisi2    | NULL    | NULL | NULL    | NULL |
  84. +----------+---------+------+---------+------+

keywords

EXPLODE_MAP,EXPLODE_MAP_OUTER,MAP