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.

grammar:

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

example

1. mysql> set enable_vectorized_engine = true

3. mysql> select e1 from (select 1 k1) as t lateral view explode([1,2,3]) tmp1 as e1;
4. +------+
5. | e1   |
6. +------+
7. |    1 |
8. |    2 |
9. |    3 |
10. +------+

12. mysql> select e1 from (select 1 k1) as t lateral view explode_outer(null) tmp1 as e1;
13. +------+
14. | e1   |
15. +------+
16. | NULL |
17. +------+

19. mysql> select e1 from (select 1 k1) as t lateral view explode([]) tmp1 as e1;
20. Empty set (0.010 sec)

22. mysql> select e1 from (select 1 k1) as t lateral view explode([null,1,null]) tmp1 as e1;
23. +------+
24. | e1   |
25. +------+
26. | NULL |
27. |    1 |
28. | NULL |
29. +------+

31. mysql> select e1 from (select 1 k1) as t lateral view explode_outer([null,1,null]) tmp1 as e1;
32. +------+
33. | e1   |
34. +------+
35. | NULL |
36. |    1 |
37. | NULL |
38. +------+

keywords

EXPLODE,EXPLODE_OUTER,ARRAY