Skip to main content

variant_explode table-valued function

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

Returns a set of rows by un-nesting input.

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

Syntax

variant_explode ( input )

Arguments

  • variantExpr: A VARIANT expression, representing a VARIANT object or VARIANT ARRAY.

Returns

A set of rows composed of the elements of the VARIANT ARRAYor the keys and values of the VARIANT object. The columns produced by variant_explode are:

  • pos INT
  • key STRING
  • value VARIANT.

When exploding a VARIANT object, the output key and value columns represent the keys and values of the object. When exploding a VARIANT array, the output key is always NULL, and the output value column represents the elements of the array.

If input is NULL, or is not a VARIANT ARRAY or VARIANT object, no rows are produced. To produce a single row of NULL values in this case, use the variant_explode_outer function.

Examples

SQL
-- Simple example
> SELECT *
FROM variant_explode(parse_json('[1, "a", {"b": "hello"}]'));
pos key value
--- ---- -------------
0 NULL 1
1 NULL "a"
2 NULL {"b":"hello"}

> SELECT *
FROM variant_explode(parse_json('{"foo":1,"bar":"hello"}'));
pos key value
--- ---- -------------
0 bar "hello"
1 foo 1

-- Using lateral correlation
> SELECT t.value AS outer, u.value AS inner
FROM variant_explode(parse_json('[[1, 2], [3, 4]]')) AS t,
LATERAL variant_explode(t.value) AS u;
outer inner
----- -----
[1,2] 1
[1,2] 2
[3,4] 3
[3,4] 4