> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# VARIANT_GET

> Extracts a VARIANT value at a path and converts it to a requested SQL type.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Scalar function</span></div>

NULL value/path, unresolved/invalid paths or failed supported typed casts produce NULL.

## Syntax

### Scalar

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
variant_get(value, path, target_type)
```

## Arguments

| Name | Type | Required | Description |
| - | - | - | - |
| value | VARIANT | Yes | Value to navigate. |
| path | STRING | Yes | Path into value. Must be a constant argument. |
| target\_type | STRING literal containing a SQL type | Yes | Use BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, BINARY, DATE, TIME, TIMESTAMP, DECIMAL(p, s), VARIANT, ARRAY\<T>, or STRUCT\<field: T, ...>. T names an element or field type from this list; arrays and structs can be nested. For DECIMAL(p, s), p is precision and s is scale; see the return-type exception below. Must be a constant argument. |

## 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

| Property | Value |
| - | - |
| Deterministic | Yes |
| Supports window usage | No |
| Exact result | Partial (Conversion to the requested type can lose precision or return NULL.) |

## 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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT variant_get(parse_json('{"a":7}'), '$.a', 'INT') AS "value";
```

| value |
| - |
| 7 |

Request a decimal, an array, and a struct. The decimal result has scale 18, even though the request specifies scale 2:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(json_text) AS (VALUES ('{"amount":"12.34","items":[1,2],"obj":{"x":7}}')) SELECT variant_get(parse_json(json_text), '$.amount', 'DECIMAL(10,2)') AS amount, variant_get(parse_json(json_text), '$.items', 'ARRAY<INT>') AS items, variant_get(parse_json(json_text), '$.obj', 'STRUCT<x:INT>') AS obj FROM t;
```

| amount | items | obj |
| - | - | - |
| 12.340000000000000000 | \[1,2] | \{"x":7} |

## 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.

## Related functions

* [get\_path](/query/sql/functions/json/get_path)
* [try\_variant\_get](/query/sql/functions/json/try_variant_get)


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.