docs/sql-manual/sql-functions/table-functions/explode.md

{ “title”: “EXPLODE”, “language”: “en” }

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 |
+------+