Skip to main content

variant_pick function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 19 and above

Returns a VARIANT containing only the fields and array elements of variantExpr selected by the specified paths.

Syntax​

SQL
variant_pick ( variantExpr, path1 [, path2, ...] )

Arguments​

  • variantExpr: A VARIANT expression.
  • path1, path2, ...: One or more STRING expressions with well-formed JSON path expressions specifying the paths to keep. Array indexes are zero-based.

Returns​

A value of type VARIANT.

Selected fields and elements retain their parent structure. Arrays contain only selected elements, in their original order.

Path order and duplicates do not affect the result. Selecting a parent includes all its children. The root path $ selects the entire value.

Missing fields, out-of-range indexes, and incompatible intermediate types are ignored. With no matches, object and array inputs return {} and [], respectively. Scalars and variant nulls are unchanged.

If variantExpr is SQL NULL, the result is SQL NULL. SQL NULL paths are skipped.

Malformed paths raise INVALID_VARIANT_PATH.

Common error conditions​

Examples​

SQL
-- Select object fields
> SELECT variant_pick(parse_json('{"a": 1, "b": 2, "c": 3}'), '$.a', '$.c')
{"a":1,"c":3}

-- Select a nested field
> SELECT variant_pick(parse_json('{"a": {"b": 1, "c": 2}, "d": 3}'), '$.a.b')
{"a":{"b":1}}

-- Preserve array order and ignore duplicate paths
> SELECT variant_pick(parse_json('[10, 20, 30, 40]'), '$[2]', '$[0]', '$[2]')
[10,30]

-- Overlapping paths
> SELECT variant_pick(parse_json('{"a": {"b": 1, "c": 2}}'), '$.a.b', '$.a')
{"a":{"b":1,"c":2}}

-- Missing path
> SELECT variant_pick(parse_json('{"a": {"b": 1}}'), '$.a.missing')
{}

-- Skip a SQL NULL path
> SELECT variant_pick(parse_json('{"a": 1, "b": 2}'), '$.a', CAST(NULL AS STRING))
{"a":1}