try_variant_get
function
Applies to: Databricks SQL Databricks Runtime 15.3 and later
Extracts a value of type type
from variantExpr
, specified by path
, or NULL
if it is not possible to cast to the target type.
Arguments
variantExpr
: AVARIANT
expression.path
: ASTRING
literal with a well-formed JSON path expression.type
: ASTRING
literal defining the type.
Returns
A value of type type
.
If the object cannot be found or it cannot be cast to type
, NULL
is returned.
To raise an error when the cast fails use variant_get.
Examples
-- Simple example
> SELECT try_variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.data[1].a', 'string')
hello
-- missing path
> SELECT try_variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.missing', 'int')
null
-- Invalid cast
> SELECT try_variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.key', 'array<int>')
null