EXPLODE
Descriptionβ
The explode
function takes an array as input and maps each element of the array to a separate row. It is typically used in conjunction with LATERAL VIEW to flatten nested data structures into a standard tabular format. The main difference between explode and explode_outer
lies in handling empty values.
Syntaxβ
EXPLODE(<array>)
EXPLODE_OUTER(<array>)
Required Parametersβ
Parameter | Description |
---|---|
<arr> | Array type |
Return Valueβ
When the array is not empty or NULL, the return values of explode
and explode_outer
are the same.
When the data is empty or NULL:
explode
will not produce any rows and will filter out these records.
explode_outer
if the array is empty, will generate a single row, but the expanded column value will be NULL. If the array is NULL, it will also retain a row and return NULL.
Examplesβ
select e1 from (select 1 k1) as t lateral view explode([1,2,3]) tmp1 as e1;
+------+
| e1 |
+------+
| 1 |
| 2 |
| 3 |
+------+
select e1 from (select 1 k1) as t lateral view explode_outer(null) tmp1 as e1;
+------+
| e1 |
+------+
| NULL |
+------+
select e1 from (select 1 k1) as t lateral view explode([]) tmp1 as e1;
Empty set (0.010 sec)
select e1 from (select 1 k1) as t lateral view explode([null,1,null]) tmp1 as e1;
+------+
| e1 |
+------+
| NULL |
| 1 |
| NULL |
+------+
select e1 from (select 1 k1) as t lateral view explode_outer([null,1,null]) tmp1 as e1;
+------+
| e1 |
+------+
| NULL |
| 1 |
| NULL |
+------+