VARIANT path access and casts
The problem
You loaded JSON into a VARIANT column. You want user_id as an integer, email as text, and a filter on event_type. The colon syntax looks like a field access in an object-oriented language, so it is easy to treat the result as already typed. Official Snowflake documentation says otherwise: the : operator, and the . / [] that follow it, always return VARIANT.
A working example
Snowflake syntax from official semi-structured documentation (not executed on Snowflake):
SELECT
src:dealership, -- VARIANT; displayed with quotes around the string
src:dealership::VARCHAR AS dealership,
src:vehicle[0].price::NUMBER * 0.10 AS tax,
src:salesperson.id::INTEGER AS salesperson_id
FROM car_sales;
Official docs use a car_sales example and show two facts worth memorizing:
src:dealershipandsrc:dealership::VARCHARdo not display the same way. Without the cast, VARCHAR values pulled out of VARIANT are shown with double quotes around them.- Arithmetic on a price path is done after
::NUMBER(their tax example issrc:vehicle[0].price::NUMBER * 0.10).
GET_PATH is the function form of the same idea. Official docs treat src:vehicle[0].make as shorthand for GET_PATH(src, 'vehicle[0].make').
A close cousin of the “forgot to cast” bug was executed in DuckDB 2.0.0 with JSON extract functions (this is not Snowflake VARIANT):
SELECT
json_extract('{"n": "10"}', '$.n') = '10' AS cmp_json_to_text,
json_extract_string('{"n": "10"}', '$.n') = '10' AS cmp_text_to_text;
Result: cmp_json_to_text was FALSE; cmp_text_to_text was TRUE. Comparing an extracted JSON value to a SQL string is not the same as comparing two strings. On Snowflake, prefer an explicit :: cast (or TO_VARCHAR / TO_NUMBER) before you compare, join, or display a path.
PARSE_JSON has a second documented NULL trap: a SQL NULL input yields SQL NULL, but the string 'null' becomes a VARIANT null — a real value, not a SQL NULL. Filters written as payload:field IS NULL will not treat those the same way.
The gotcha
Because path access returns VARIANT, a comparison or join can run in VARIANT-land rather than in the type you had in mind. Official docs do not publish a full matrix of VARIANT equality quirks on that page; they do tell you to cast to the type you mean. Do that at the path, not later in a downstream tool that happens to render the value nicely.
payload:items is still a VARIANT holding an array. It does not automatically unnest. Use FLATTEN when you need one row per element.
Bracket indexes are 0-based in the official examples (vehicle[0]). Asking for [1] on a one-element array is not an error; you just get a missing path.
When not to use this
- You are about to query the same payload in twenty models. Flatten (or ingest) into typed columns once, then query those.
- You need every key in an object of unknown shape —
LATERAL FLATTENon the object (officialMODE => 'OBJECT') is the right tool, not a long list of colon paths. - The value is XML rather than JSON; official docs point you at
XMLGET/PARSE_XMLinstead of colon paths.
Snowflake colon-path behavior, the “always returns VARIANT” rule, the quoted-vs-cast display example, GET_PATH equivalence, and the PARSE_JSON SQL-NULL vs VARIANT-null distinction are from official Snowflake documentation and were not executed on Snowflake. The JSON-extract comparison (FALSE without a string extract, TRUE with it) was executed in DuckDB 2.0.0 and is only an analogue.