posexplode_outer table-valued generator function

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

Returns rows by un-nesting the array with numbering of positions using OUTER semantics.




  • expr: An ARRAY or MAP expression.


A set of rows composed of the other expressions in the select list, the position of the elements in the array or map, and the elements of the array, or keys and values of the map.

If expr is NULL, a single row with NULLs for the array or map values.

The columns produced by posexplode_outer of an array are named pos and col by default, but can be aliased. You can also alias them using an alias tuple such as AS (myPos,  myValue).

The columns for maps are by default called pos, key, and value. You can also alias them using an alias tuple such as AS (myPos, myKey, myValue).

You can place posexplode_outeronly 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.


> SELECT posexplode_outer(array(10, 20)) AS (r, elem), 'Spark';
 0  10 Spark
 1  20 Spark
> SELECT posexplode_outer(map(1, 'a', 2, 'b')) AS (r, num, val), 'Spark';
 0  1   a   Spark
 1  2   b   Spark
> SELECT posexplode_outer(cast(NULL AS array<int>)), 'Spark';
 NULL   Spark