explode_outer table-valued generator function

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

Returns rows by un-nesting expr using outer semantics.




  • 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 a single row with NULLs for the array or map values is produced.

explode_outer 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_outer(array(10, 20)) AS elem, 'Spark';
 10 Spark
 20 Spark

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

> SELECT explode_outer(cast(NULL AS array<int>)), 'Spark';
 NULL   Spark

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