variant_pick function
Applies to: Databricks SQL
Databricks Runtime 19 and above
Returns a VARIANT containing only the fields and array elements of variantExpr selected by the specified paths.
Syntax
variant_pick ( variantExpr, path1 [, path2, ...] )
Arguments
variantExpr: AVARIANTexpression.path1,path2, ...: One or moreSTRINGexpressions 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
-- 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}