posexplode 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.




  • 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, no rows are produced.

The columns produced by posexplode 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 pos_explode only 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(array(10, 20)) AS (r, elem), 'Spark';
 0  10 Spark
 1  20 Spark
> SELECT posexplode(map(1, 'a', 2, 'b')) AS (r, num, val), 'Spark';
 0  1   a   Spark
 1  2   b   Spark