posexplode table-valued generator function

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

Returns a set of rows by un-nesting expr with numbering of positions.

In Databricks Runtime 16.1 and above this function supports named parameter invocation.

Syntax 

posexplode(collection)

Arguments

  • collection: An ARRAY or MAP expression.

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 collection is NULL no rows are produced.

  • Applies to: check marked yes Databricks Runtime 12.1 and earlier:

    posexplode can only be placed in the SELECT list as the root of an expression or following a LATERAL VIEW. When placing the function in the SELECT list there must be no other generator function in the same SELECT list or UNSUPPORTED_GENERATOR.MULTI_GENERATOR is raised.

  • Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 LTS and above:

    Invocation from the LATERAL VIEW clause or the SELECT list is deprecated. Instead, invoke posexplode as a table_reference.

Examples

Applies to: check marked yes 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: check marked yes Databricks SQL check marked yes 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