explode

description

Table functions must be used in conjunction with Lateral View.

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

syntax

  1. explode(expr)
  2. explode_outer(expr)

example

  1. mysql> select e1 from (select 1 k1) as t lateral view explode([1,2,3]) tmp1 as e1;
  2. +------+
  3. | e1   |
  4. +------+
  5. |    1 |
  6. |    2 |
  7. |    3 |
  8. +------+
  10. mysql> select e1 from (select 1 k1) as t lateral view explode_outer(null) tmp1 as e1;
  11. +------+
  12. | e1   |
  13. +------+
  14. | NULL |
  15. +------+
  17. mysql> select e1 from (select 1 k1) as t lateral view explode([]) tmp1 as e1;
  18. Empty set (0.010 sec)
  20. mysql> select e1 from (select 1 k1) as t lateral view explode([null,1,null]) tmp1 as e1;
  21. +------+
  22. | e1   |
  23. +------+
  24. | NULL |
  25. |    1 |
  26. | NULL |
  27. +------+
  29. mysql> select e1 from (select 1 k1) as t lateral view explode_outer([null,1,null]) tmp1 as e1;
  30. +------+
  31. | e1   |
  32. +------+
  33. | NULL |
  34. |    1 |
  35. | NULL |
  36. +------+

keywords

EXPLODE,EXPLODE_OUTER,ARRAY