MAP_AGG

description

Syntax

MAP_AGG(expr1, expr2)

Returns a map consists of expr1 as the key and expr2 as the corresponding value.

example

  1. MySQL > select `n_nationkey`, `n_name`, `n_regionkey` from `nation`;
  2. +-------------+----------------+-------------+
  3. | n_nationkey | n_name         | n_regionkey |
  4. +-------------+----------------+-------------+
  5. |           0 | ALGERIA        |           0 |
  6. |           1 | ARGENTINA      |           1 |
  7. |           2 | BRAZIL         |           1 |
  8. |           3 | CANADA         |           1 |
  9. |           4 | EGYPT          |           4 |
  10. |           5 | ETHIOPIA       |           0 |
  11. |           6 | FRANCE         |           3 |
  12. |           7 | GERMANY        |           3 |
  13. |           8 | INDIA          |           2 |
  14. |           9 | INDONESIA      |           2 |
  15. |          10 | IRAN           |           4 |
  16. |          11 | IRAQ           |           4 |
  17. |          12 | JAPAN          |           2 |
  18. |          13 | JORDAN         |           4 |
  19. |          14 | KENYA          |           0 |
  20. |          15 | MOROCCO        |           0 |
  21. |          16 | MOZAMBIQUE     |           0 |
  22. |          17 | PERU           |           1 |
  23. |          18 | CHINA          |           2 |
  24. |          19 | ROMANIA        |           3 |
  25. |          20 | SAUDI ARABIA   |           4 |
  26. |          21 | VIETNAM        |           2 |
  27. |          22 | RUSSIA         |           3 |
  28. |          23 | UNITED KINGDOM |           3 |
  29. |          24 | UNITED STATES  |           1 |
  30. +-------------+----------------+-------------+
  32. MySQL > select `n_regionkey`, map_agg(`n_nationkey`, `n_name`) from `nation` group by `n_regionkey`;
  33. +-------------+---------------------------------------------------------------------------+
  34. | n_regionkey | map_agg(`n_nationkey`, `n_name`)                                          |
  35. +-------------+---------------------------------------------------------------------------+
  36. |           1 | {1:"ARGENTINA", 2:"BRAZIL", 3:"CANADA", 17:"PERU", 24:"UNITED STATES"}    |
  37. |           0 | {0:"ALGERIA", 5:"ETHIOPIA", 14:"KENYA", 15:"MOROCCO", 16:"MOZAMBIQUE"}    |
  38. |           3 | {6:"FRANCE", 7:"GERMANY", 19:"ROMANIA", 22:"RUSSIA", 23:"UNITED KINGDOM"} |
  39. |           4 | {4:"EGYPT", 10:"IRAN", 11:"IRAQ", 13:"JORDAN", 20:"SAUDI ARABIA"}         |
  40. |           2 | {8:"INDIA", 9:"INDONESIA", 12:"JAPAN", 18:"CHINA", 21:"VIETNAM"}          |
  41. +-------------+---------------------------------------------------------------------------+
  43. MySQL > select n_regionkey, map_agg(`n_name`, `n_nationkey` % 5) from `nation` group by `n_regionkey`;
  44. +-------------+------------------------------------------------------------------------+
  45. | n_regionkey | map_agg(`n_name`, (`n_nationkey` % 5))                                 |
  46. +-------------+------------------------------------------------------------------------+
  47. |           2 | {"INDIA":3, "INDONESIA":4, "JAPAN":2, "CHINA":3, "VIETNAM":1}          |
  48. |           0 | {"ALGERIA":0, "ETHIOPIA":0, "KENYA":4, "MOROCCO":0, "MOZAMBIQUE":1}    |
  49. |           3 | {"FRANCE":1, "GERMANY":2, "ROMANIA":4, "RUSSIA":2, "UNITED KINGDOM":3} |
  50. |           1 | {"ARGENTINA":1, "BRAZIL":2, "CANADA":3, "PERU":2, "UNITED STATES":4}   |
  51. |           4 | {"EGYPT":4, "IRAN":0, "IRAQ":1, "JORDAN":3, "SAUDI ARABIA":0}          |
  52. +-------------+------------------------------------------------------------------------+

keywords

MAP_AGG