Querying VARIANT Columns With Colon Syntax in Snowflake

Snowflake's VARIANT column type holds JSON, and its colon syntax is the nicest JSON path syntax of any database: doc:customer.name::string reads like a field access followed by a cast. It also hides three behaviors that matter, which are that the result of a path is still a VARIANT, that JSON null and SQL NULL are different things, and that key names are case sensitive. This article explains the syntax, the casts, the null rules and the pattern for loading JSON, so that the first queries on a new table give the answers you expect.

Getting JSON into a VARIANT

The usual way is to land the raw document in a table with one VARIANT column:

CREATE TABLE raw_orders (doc VARIANT);

INSERT INTO raw_orders
SELECT PARSE_JSON('{"id":1,"customer":{"name":"Ada","tier":"gold"},"items":[{"sku":"A1","qty":2},{"sku":"B2","qty":1}],"note":null}');

PARSE_JSON(text) turns a string into a VARIANT and raises an error if the string is not valid JSON; TRY_PARSE_JSON returns NULL instead. For files, COPY INTO raw_orders FROM @stage FILE_FORMAT = (TYPE = JSON) loads each document into the VARIANT column. A file format option strips an outer array so each element becomes a row. Keeping the raw document in its own table, and building typed tables from it in a second step, is the standard pattern: a surprise in the data breaks the second step, which you can fix and rerun, and not the load.

The colon path

Select a key with a colon, descend with dots, index arrays with brackets:

SELECT doc:id,
       doc:customer.name,
       doc:customer.tier,
       doc:items[0].sku,
       doc:items[1].qty
FROM raw_orders;

Each of these is a VARIANT. In the result grid, strings show with double quotes ("Ada") and numbers without. That is the sign you have not cast. A VARIANT compared or joined as if it were a string can behave surprisingly, and a VARIANT put into a typed table column needs a cast. So write the cast every time:

SELECT doc:id::number                AS id,
       doc:customer.name::string     AS customer,
       doc:items[0].qty::int         AS first_qty
FROM raw_orders;

:: is Snowflake's cast operator. The common targets are string (or varchar), number(p,s), int, float, boolean, date, timestamp_ntz. A cast that cannot convert raises an error; TRY_CAST(doc:qty AS int) returns NULL instead, and is the safer choice for dirty data. There are also strict typed accessors such as AS_INTEGER and AS_VARCHAR that return NULL if the VARIANT is of another type, which makes them useful as type checks: AS_INTEGER(doc:qty) is NULL when qty is a string.

Bracket notation

The same navigation works with brackets, which you need when a key contains characters that a dotted path cannot express:

SELECT doc['customer']['name']::string,
       doc:"order-id"::string,
       doc['order-id']::string
FROM raw_orders;

A key with a hyphen, space or dot needs brackets or double quotes. Both doc['customer']['name'] and doc:customer.name mean the same thing, and the dotted form is shorter for ordinary names. The function forms exist too: GET(doc, 'customer') for one step and GET_PATH(doc, 'customer.name') for a path given as a string, which is how you pass a computed path.

Case sensitivity

JSON keys are case sensitive, and the path is too: doc:Customer.Name returns NULL for a document whose keys are customer and name. Snowflake's own object names (columns, tables) are case insensitive unless quoted, which makes the contrast sharper. The first time a column of NULLs appears from a path that looks right, compare the case of the key. OBJECT_KEYS(doc) returns the keys of an object as an array so you can look.

Null: three situations

JSON gives you three states for a field, and Snowflake maps them as follows according to its documentation:

A JSON null is a real VARIANT value, not SQL NULL. A filter written WHERE doc:note IS NULL finds documents where the key is missing and misses the ones with an explicit null. Casting normalizes both: doc:note::string IS NULL is true for the first two cases. IS_NULL_VALUE(doc:note) is true only for the explicit null, which lets you tell the cases apart when you must. STRIP_NULL_VALUE(doc:note) converts a VARIANT null to SQL NULL. The simplest policy for queries is to cast first and test the cast.

Compare the same question in other engines: PostgreSQL's -> returns the JSONB null and ->> returns SQL NULL (querying JSONB with the arrow operators), and MySQL's ->> returns the four-letter text null (creating and querying a MySQL JSON column). Each engine has its own trap.

Type inspection and arrays

TYPEOF(doc:items) returns the type name (ARRAY, OBJECT, VARCHAR, INTEGER, DECIMAL, DOUBLE, BOOLEAN, NULL_VALUE), which is the right way to audit a column whose contents you do not control: SELECT TYPEOF(doc:qty), COUNT(*) FROM raw_orders GROUP BY 1 shows whether qty is always a number. ARRAY_SIZE(doc:items) counts elements without unpacking, and doc:items[0] addresses by position; an index past the end returns NULL. To turn an array into rows you use FLATTEN (flattening nested JSON with LATERAL FLATTEN), the Snowflake counterpart of OPENJSON (in SQL Server) and json_each (in SQLite).

Comparisons and joins

Because paths return VARIANT, WHERE doc:customer.tier = 'gold' compares a VARIANT with a string. Snowflake accepts it, and for clarity and predictable behavior I would cast: WHERE doc:customer.tier::string = 'gold'. For numbers, doc:qty::int > 5 is a numeric comparison, and a VARIANT holding a string "7" would not pass it silently: the cast either converts or fails according to the cast you chose. When joining to a typed table, cast the VARIANT side to the type of the other column; a join on an uncast VARIANT may not use the same pruning and typing as a join on typed columns.

Performance notes from the documentation

Snowflake stores VARIANT in a columnar form. According to its documentation, it collects statistics and, when the data is regular enough, stores frequently occurring paths in separate sub-columns, which is why a query that reads two paths out of a big VARIANT can scan far less data than the whole document. The same documentation notes that this works best for data of consistent structure, for simple data types, and that there are limits on the size of a single VARIANT value (check the current figure for your account). The practical advice in the docs is the one that every database teaches: for fields used in filters and joins, extract them into typed columns in a transformation step. A CREATE TABLE orders AS SELECT doc:id::number AS id, doc:customer.name::string AS customer, doc FROM raw_orders keeps the document and adds the typed fields next to it.

For the shape of this on the open-source engines I could test, see querying JSON files with DuckDB, which has the same schema-on-read idea on a laptop. The overview across all four commercial engines is in JSON in SQL Server, Oracle, Snowflake and BigQuery compared. To look at a document before you load it, the JSON formatter runs in the browser.

When VARIANT paths are not the right approach

The fields are the same in every row. Typed columns are smaller and clearer. Use VARIANT for the landing zone and the genuinely variable part.

You compare or join a lot. Cast once into a typed table rather than casting in every query.

You rely on case-insensitive key matching. There is none; normalize key case on the way in or in the typed layer.

The documents are very large. A single VARIANT value has a size limit, and large documents defeat the sub-column extraction.

You need to prove null handling. Test the three cases (missing, null, value) on your data; do not trust the table above for your version without running it.

The reference is Snowflake's querying semi-structured data and the VARIANT data type pages.