variant_explode_outer table-valued function

  • Article

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

Returns a set of rows by un-nesting variantExpr using outer semantics.

Syntax

variant_explode_outer ( variantExpr )

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 variantExpr is NULL, or is not either a VARIANT ARRAY with at least one element or an OBJECT with at least one field, a single row of NULLs is produced. To return no rows in this case use the variant_explode function.

Examples

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

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

 -- null input
> SELECT value FROM variant_explode_outer(null) AS t(pos, key, value);
  null

-- Not an array or object input
> SELECT value FROM variant_explode_outer(parse_json('123')) AS t(pos, key, value);
  null

-- Using lateral correlation
> SELECT t.value AS outer, u.value AS inner
    FROM variant_explode_outer(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