explode table-valued generator function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns rows by un-nesting expr.




  • expr: An ARRAY or MAP expression.


A set of rows composed of the other expressions in the select list and either the elements of the array or the keys and values of the map. If expr is NULL no rows are produced.

explode can only be placed in the select list or a LATERAL VIEW. When placing the function in the SELECT list there must be no other generator function in the same SELECT list.

The column produced by explode of an array is named col by default, but can be aliased. The columns for a map are by default called key and value. They can also be aliased using an alias tuple such as AS (myKey, myValue).


> SELECT explode(array(10, 20)) AS elem, 'Spark';
 10 Spark
 20 Spark

> SELECT explode(map(1, 'a', 2, 'b')) AS (num, val), 'Spark';
 1   a   Spark
 2   b   Spark

> SELECT explode(array(1, 2)), explode(array(3, 4));
  Error: unsupported generator