Skip to main content

Categories: Variant Type

VARIANT_GET Preview

Extracts a value from a VARIANT using a path expression, optionally casting to a target type.

Syntax​

VARIANT_GET(variant VARIANT, path VARCHAR) → VARIANT​

  • variant: A VARIANT value containing scalars, arrays, or objects. VARIANT_GET can extract scalar values and arrays of scalar values. When the path resolves to an object or an array of objects, navigate deeper into the path to reach a scalar value.
  • path: A path expression starting with $ that specifies which element to extract. Use dot notation for fields ($.field), brackets for array elements ($.items[0]), and chained paths for nested access ($.parent.child).

Examples

VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"name": "Alice"}'), '$.name')
-- (VARIANT) "Alice"

VARIANT_GET(variant VARIANT, path VARCHAR AS type) → Type​

  • variant: A VARIANT value containing scalars, arrays, or objects. VARIANT_GET can extract scalar values and arrays of scalar values. When the path resolves to an object or an array of objects, navigate deeper into the path to reach a scalar value.
  • path: A path expression starting with $ followed by AS and the target type. See Supported cast targets for the full list of supported types.

Examples

VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"age": 30}'), '$.age' AS INT)
-- 30
VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"user": {"city": "Seattle"}}'), '$.user.city' AS VARCHAR)
-- Seattle
VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"items": [{"name": "Widget"}]}'), '$.items[0].name' AS VARCHAR)
-- Widget
VARIANT_GET example 
SELECT VARIANT_GET(data, '$.user_id' AS VARCHAR) FROM events
-- -- Returns user_id from each row
VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"scores": [95, 87, 92]}'), '$.scores' AS ARRAY<INT>)
-- [95, 87, 92]
VARIANT_GET example 
SELECT FLATTEN(VARIANT_GET(PARSE_JSON('{"tags": ["a", "b", "c"]}'), '$.tags' AS ARRAY<VARCHAR>))
-- -- Returns one row per array element: a, b, c
VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"price": 19.99}'), '$.price' AS DECIMAL(10,2))
-- 19.99
VARIANT_GET example 
SELECT VARIANT_GET(PARSE_JSON('{"created": "2025-03-15 10:30:00"}'), '$.created' AS TIMESTAMP)
-- 2025-03-15 10:30:00

Usage Notes​

Path syntax

VARIANT_GET uses path expressions to navigate into nested structures. The path always starts with $, which represents the root of the VARIANT value.

PathMeaning
$The root (the entire VARIANT)
$.fieldA field named "field" at the root
$.parent.childNested field access
$.array[0]First element of an array (zero-indexed)
$["field"]Bracket notation (equivalent to $.field)

Wildcard paths (e.g., $.items[*].name) are not supported. You must specify explicit field names and array indices.

Supported cast targets

When using VARIANT_GET with AS, the target type must be a scalar type or an array of a scalar type. The following are supported:

CategorySupported types
NumericINT, BIGINT, FLOAT, DOUBLE, DECIMAL(p,s)
StringVARCHAR
BinaryVARBINARY
BooleanBOOLEAN
Date/TimeDATE, TIME, TIMESTAMP
IdentifierUUID
ArrayARRAY<scalar_type> (e.g., ARRAY<VARCHAR>, ARRAY<INT>), ARRAY<VARIANT>

When using DECIMAL without precision and scale (e.g., AS DECIMAL), values are truncated to integers. Use AS DECIMAL(p,s) to preserve decimal places (e.g., AS DECIMAL(10,2) for two decimal places).

TIMESTAMP values must use a space separator between date and time (e.g., "2025-03-15 10:30:00"). The ISO 8601 format with a T separator (e.g., "2025-03-15T10:30:00") is not supported and will produce a parse error.

Type checking before extraction

Use type-checking functions like IS_BOOLEAN to verify a value's type before extracting:

SELECT 
  CASE 
    WHEN IS_BOOLEAN(variant_get(payload, '$.active')) 
    THEN variant_get(payload, '$.active' AS BOOLEAN)
    ELSE NULL 
  END AS is_active
FROM events;

When called without AS, VARIANT_GET returns a VARIANT, which can be passed to type-checking functions. When called with AS, it extracts and casts to the specified type.

Missing paths vs VARIANT null

When a path does not exist, VARIANT_GET returns SQL NULL. When a path exists but contains a VARIANT null value, VARIANT_GET returns a VARIANT null. Casting a VARIANT null to a target type (e.g., VARIANT_GET(v, '$.key', 'INT')) returns SQL NULL.

ScenarioIS NULLIS_VARIANT_NULL
Path exists, value is VARIANT nullFALSETRUE
Path does not existTRUEFALSE

Use IS_VARIANT_NULL to distinguish between these cases.

Array type coercion

Variant arrays can contain mixed types (e.g., [1, "two", null, 123.456]), but Dremio arrays require all elements to share the same type. If any element cannot be converted to the target type, VARIANT_GET fails and TRY_VARIANT_GET returns NULL. To extract a mixed-type array of primitive values, use ARRAY<VARCHAR> — all primitive variant types can be converted to strings. For arrays that may contain complex types (objects or nested arrays), use ARRAY<VARIANT>.

Type conversion behavior

VARIANT_GET follows the same conversion rules as CAST. For example, VARIANT_GET(TO_VARIANT(123.456), '$' AS VARCHAR) returns "123.456". This also applies to arrays: VARIANT_GET(PARSE_JSON('[1, "two", null, 123.456]'), '$' AS ARRAY<VARCHAR>) returns ["1","two",null,"123.456"].

Error handling

If the target type is specified and the cast fails, VARIANT_GET raises an error. Use TRY_VARIANT_GET to return NULL instead.

Common causes of errors include:

  • Missing the dot separator after $ in path expressions (e.g., using $field instead of $.field).
  • Path resolves to an object or array of objects. Navigate deeper to reach a scalar value.
  • Using an incompatible value format for the target type. For TIMESTAMP, use a space-separated format ("2025-03-15 10:30:00") instead of ISO 8601 with T.
  • Extracting a mixed-type array with a non-VARCHAR target type. Use ARRAY<VARCHAR> for arrays of mixed primitives, or ARRAY<VARIANT> for arrays that contain complex types.