Snowflake SQL Patterns

VARIANT path access and casts

Colon paths return VARIANT, not VARCHAR. Cast explicitly or comparisons and output will surprise you.

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:

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

Verification

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.

Official docs