Querying VARIANT Data with variant_get, Path Syntax, and Variant Functions

1.Navigating a VARIANT with path operators

The examples on this page query a table store_data that has one VARIANT column, raw. It holds a nested store document with top-level fields such as owner, zip code, and fb:testid, and an object store that contains arrays fruit and book and an object bicycle. You navigate VARIANT with the same SQL operators used for JSON strings: : selects a top-level field, . or [<key>] selects a nested field, and [<index>] selects an array element. Array indices are 0-based.

-- Use dot notation
SELECT raw:store.bicycle FROM store_data

-- Use brackets
SELECT raw:store['bicycle'] FROM store_data

This catches teams migrating from JSON strings: JSON string paths are case-insensitive, but every VARIANT path element is matched case-sensitively. A path that does not exist, or an array index that is out of bounds, returns NULL rather than an error, so a wrong case gives you a column of NULLs, not a failure. A second migration difference: the [*] wildcard for selecting or unpacking every array element is not supported on VARIANT.

Field names with special characters need escaping. The ['<field>'] form escapes any special character: spaces, periods, colons, and square brackets. Backticks escape only spaces and colons. If a field name contains a period or brackets and you escape it with backticks, you get NULL.

-- Enclose a field name that contains special characters in single quotes inside square brackets.
SELECT raw:['zip code'], raw:['fb:testid'] FROM store_data

Sources12

2.Typed extraction with variant_get and try_variant_get

The : operator returns a VARIANT. That is why raw:owner displays as "amy", quotes included. To get a typed SQL value, use variant_get(variantExpr, path, type). The path is a JSON path string literal starting at $, and the type is a string literal such as 'string', 'int', or 'double'. The same function exists in PySpark, e.g. variant_get(col("raw"), "$.owner", "string").

-- Simple example
> SELECT variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.data[1].a', 'string')
  hello

-- missing path
> SELECT variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.missing', 'int')
  null

-- Invalid cast
> SELECT variant_get(parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}'), '$.key', 'array<int>')
  Error: INVALID_VARIANT_CAST.

variant_get treats the two failure modes differently. A missing path is not an error: you get NULL. A value that is found but cannot be cast to the requested type raises INVALID_VARIANT_CAST. try_variant_get takes the same arguments but returns NULL in both cases, which makes it the safe option for messy data. The SQL shortcuts work the same way: :: (or cast) casts a path result and behaves like variant_get, while ?:: is the error-tolerant form that matches try_variant_get. :: can also cast a nested object straight into a STRUCT, for example raw:store.bicycle::STRUCT<price DOUBLE, color STRING>.

-- These are synonymous to:
> SELECT '{"key": 123, "data": [4, {"a": "hello"}, "str"]}':data[1].a ?::STRING;
 hello

SELECT  ? (raw, '$.store.bicycle.price', 'double') as price FROM store_data

Sources32

3.Variant NULLs, exploding, and schema inspection

Each VARIANT function has a JSON-string counterpart, but VARIANT handles casting and NULLs differently from that counterpart. Learn this mapping and you can translate existing JSON-string queries:

A variant can hold two kinds of NULL. A SQL NULL means the value is missing. A variant NULL means the document explicitly contains null. is_variant_null returns true only for a variant NULL, so a missing path, which is a SQL NULL, gives false. The reverse is also true: IS NULL is false for an explicit JSON null, because the VARIANT value itself exists.

-- difference between is_variant_null and is null
> SELECT is_variant_null(v:key), v:key IS NULL
    FROM VALUES(parse_json('{"key": null}')) AS T(v)
  true false

Because [*] is unavailable, you flatten with variant_explode. It is a table-valued generator, so it goes in the FROM clause with LATERAL, not in the SELECT list. It returns pos, key, and value columns, and key is NULL when you explode an array. If the input is NULL or is not an array or object, variant_explode produces no rows, while variant_explode_outer produces a single row of NULLs. To find out what a document contains, schema_of_variant returns the schema of one value. schema_of_variant_agg merges the schemas across rows and falls back to VARIANT when it finds no common type.

SELECT key, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw);

SELECT pos, value
  FROM store_data,
  LATERAL variant_explode(store_data.raw:store.basket[0]);

Sources241

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.variant_get returns NULL whenever it cannot produce a value, including when the cast to the requested type fails.Why is that wrong?

   variant_get returns NULL only for a missing path. A value that is found but cannot be cast raises INVALID_VARIANT_CAST. Use try_variant_get or ?:: to get NULL instead.

   Covered in Typed extraction with variant_get and try_variant_get

2. 2.JSON-string queries carry over to VARIANT unchanged, including case-insensitive field names and the [*] wildcard for arrays.Why is that wrong?

   VARIANT path elements are matched case-sensitively, and [*] is not supported. Use variant_explode to unpack arrays.

   Covered in Navigating a VARIANT with path operators

3. 3.is_variant_null returns true for any NULL, including a missing path or a SQL NULL input.Why is that wrong?

   is_variant_null is true only when the variant explicitly stores a null. A missing value is a SQL NULL, which you test with IS NULL.

   Covered in Variant NULLs, exploding, and schema inspection

Practise it for real

Build a small VARIANT table and see each extraction and NULL behavior for yourself

1. 1.Run SELECT parse_json('{"key": 123, "data": [4, {"a": "hello"}, "str"]}') AS v and save it as a temp view or CTAS table.

   Why: parse_json is how JSON text becomes VARIANT.

   You should see: One row containing {"data":[4,{"a":"hello"},"str"],"key":123}. Key order may differ from your input.

2. 2.Query v:data[1].a, then v:DATA[1].a.

   Why: Shows 0-based indexing and case-sensitive path matching.

   You should see: The first returns "hello" as a VARIANT; the second returns NULL.

3. 3.Run variant_get(v, '$.key', 'array<int>'), then try_variant_get(v, '$.key', 'boolean').

   Why: Compares the strict and error-tolerant extractors on a bad cast.

   You should see: variant_get raises INVALID_VARIANT_CAST; try_variant_get returns NULL.

4. 4.Run SELECT * FROM variant_explode(parse_json('[1, "a", {"b": "hello"}]')).

   Why: Shows how to flatten arrays now that [*] is not available.

   You should see: Three rows with pos 0–2, key NULL, and the element values.