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

Syntax 

explode(expr)

Arguments

  • expr: An ARRAY or MAP expression.

Returns

A set of rows composed of the elements of the array or the keys and values of the map. The column produced by explode of an array is named col. The columns for a map are called key and value.

If expr is NULL no rows are produced.

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

    explode 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 and later:

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

Examples

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

> SELECT explode(array(10, 20)) AS elem, 'Spark';
 10 Spark
 20 Spark

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

> SELECT explode(array(1, 2)), explode(array(3, 4));
  Error: UNSUPPORTED_GENERATOR.MULTI_GENERATOR

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and later:

> SELECT elem, 'Spark' FROM explode(array(10, 20)) AS t(elem);
 10 Spark
 20 Spark

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

> SELECT * FROM explode(array(1, 2)), explode(array(3, 4));
 1   3
 1   4
 2   3
 2   4

-- Using lateral correlation in Databricks 12.2 and above
> SELECT * FROM explode(array(1, 2)) AS t, LATERAL explode(array(3 * t.col, 4 * t.col));
 1   3
 1   4
 2   6
 2   8