Syntax
Scalar
Arguments
Returns
- Type: The requested SQL type, except that DECIMAL(p, s) currently returns DECIMAL(38, 18). The same decimal exception applies inside arrays and structs.
- NULL behavior: NULL value/path, unresolved/invalid paths or failed supported typed casts produce NULL.
- Shape: A VARIANT target keeps the extracted value as VARIANT. ARRAY and STRUCT targets follow the requested element or field layout; failed element or field conversions can produce NULL within that layout.
- Ordering: VARIANT arrays retain element order; object-key ordering is not guaranteed.
Properties
Usage notes
- The path must be a STRING literal; a path column is not accepted in this form.
- The type argument must be a constant STRING literal, such as
'INT','ARRAY<INT>', or'STRUCT<x:INT>'. - VARIANT_GET and TRY_VARIANT_GET use the same extraction and conversion rules. Failed conversions described above return NULL.
- A VARIANT target returns VARIANT without a primitive cast.
- Deterministic: The same inputs and settings produce the same result.
Examples
Typical behavior
- Non-Snowflake context; Snowflake uses path a.
Request a decimal, an array, and a struct. The decimal result has scale 18, even though the request specifies scale 2:
Limitations
- Malformed/unsupported arguments and incompatible type specifications can fail during planning rather than return NULL.
- For a scalar DATE, TIME, TIMESTAMP, or DECIMAL target, the selected value must be text in a valid format. A JSON number requested as DECIMAL currently returns NULL; for example, use the JSON string “12.34” when requesting a decimal.
- Date/time/decimal text parsing is not applied recursively to ARRAY or STRUCT targets. For example, requesting
ARRAY<DATE>from a JSON array of date strings can produce NULL elements. Extract and convert individual values when this parsing is needed. - DECIMAL precision and scale in target_type are not preserved: DECIMAL(10, 2), for example, returns DECIMAL(38, 18). Query tools may report DOUBLE even though the returned value is decimal.
- A BINARY target requires a stored binary value. JSON strings are not decoded as binary and return NULL for this target.
- MAP targets, including MAP inside an ARRAY or STRUCT target, are not supported. These requests can raise an execution error in both VARIANT_GET and TRY_VARIANT_GET; TRY_VARIANT_GET does not turn every unsupported-type error into NULL. Use VARIANT to keep a JSON object, or STRUCT with named fields.