posexplode
table-valued generator function
Applies to: Databricks SQL Databricks Runtime
Returns a set of rows by un-nesting expr
with numbering of positions.
Returns
A set of rows composed of the position and the elements of the array or the keys and values of the map.
The columns produced by posexplode
of an array are named pos
and col
.
The columns for a map are called pos
, key
and value
.
If expr
is NULL
no rows are produced.
Applies to: Databricks Runtime 12.1 and earlier:
posexplode
can only be placed in theSELECT
list as the root of an expression or following a LATERAL VIEW. When placing the function in theSELECT
list there must be no other generator function in the sameSELECT
list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above:
Invocation from the LATERAL VIEW clause or the
SELECT
list is deprecated. Instead, invokeposexplode
as a table_reference.
Examples
Applies to: Databricks Runtime 12.1 and earlier:
> SELECT posexplode(array(10, 20)) AS elem, 'Spark';
0 10 Spark
1 20 Spark
> SELECT posexplode(map(1, 'a', 2, 'b')) AS (num, val), 'Spark';
0 1 a Spark
1 2 b Spark
> SELECT posexplode(array(1, 2)), posexplode(array(3, 4));
Error: UNSUPPORTED_GENERATOR.MULTI_GENERATOR
Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above:
> SELECT pos, col FROM posexplode(array(10, 20));
0 10
1 20
> SELECT pos, key, value FROM posexplode(map(10, 'a', 20, 'b'));
0 10 a
1 22 b
> SELECT p1.*, p2.* FROM posexplode(array(1, 2)) AS p1, posexplode(array(3, 4)) AS p2;
0 1 0 3
0 1 1 4
1 2 0 3
1 2 1 4
-- Using lateral correlation in Databricks 12.2 and above
> SELECT p1.*, p2.* FROM posexplode(array(1, 2)) AS p1, LATERAL posexplode(array(3 * p1.col, 4 * p1.col)) AS p2;
0 1 0 3
0 1 1 4
1 2 0 6
1 2 1 8