EXPLODE

EXPLODE is a user-defined table-valued function (UDTF) that expands an array or map column into multiple rows, one per element or key-value pair.

Behavior

Input type	Output
`array<T>`	One row per element. The output column is named `col`.
`map<K, V>`	One row per key-value pair. The output columns are named `key` and `value`.
NULL row	No output row is produced.

Limitations

A SELECT statement can contain only one EXPLODE function. No other table columns are allowed in the same SELECT.
EXPLODE cannot be used with the GROUP BY, CLUSTER BY, DISTRIBUTE BY, or SORT BY clause.

Syntax

explode(<var>)

Parameters

var: Required. The value must be of the array<T> or map<K, V> type.

Return value

Input type	Output columns	Notes
`array<T>`	`col`	One row per element. NULL elements within the array are returned as NULL rows.
`map<K, V>`	`key`, `value`	One row per key-value pair.

If the input row is NULL, no output row is produced.

Examples

Expand a map column

The t_table_map table has two columns: c1 (BIGINT) and t_map (MAP<STRING,BIGINT>).

Input data:

+------------+------------------+
| c1         | t_map            |
+------------+------------------+
| 1000       | {k11:86, k21:15} |
| 1001       | {k12:97, k22:2}  |
| 1002       | {k13:99, k23:1}  |
+------------+------------------+

Query:

SELECT explode(t_map) FROM t_table_map;

Result:

+-----+-------+
| key | value |
+-----+-------+
| k11 | 86    |
| k21 | 15    |
| k12 | 97    |
| k22 | 2     |
| k13 | 99    |
| k23 | 1     |
+-----+-------+

Expand an array column and handle NULL rows

The explod_array table has one column: arr array<string>.

Create the table and insert data:

CREATE TABLE explod_array (arr array<string>);

INSERT INTO TABLE explod_array VALUES
  (array('1','2','3',null,'agb')),
  (array('1','2','3',null,'ag')),
  (null);

The table contains:

+---------------------------+
| arr                       |
+---------------------------+
| ["1","2","3",null,"agb"]  |
| ["1","2","3",null,"ag"]   |
| NULL                      |
+---------------------------+

Query:

SELECT explode(arr) FROM explod_array;

Result:

+------+
| col  |
+------+
| 1    |
| 2    |
| 3    |
| NULL |
| agb  |
| 1    |
| 2    |
| 3    |
| NULL |
| ag   |
+------+

The third input row (NULL) produces no output. Individual null elements within an array are returned as NULL rows in the output.

Related functions

EXPLODE is a complex type function. For more information about functions that process ARRAY, MAP, STRUCT, and JSON data types, see Complex type functions.