VARIANT and querying JSON

Event data and API payloads arrive as JSON, and Snowflake lets you query it directly without a fixed schema.

A VARIANT column can hold any JSON-like value: object, array, string, number, boolean or null. Load JSON files with COPY INTO using TYPE = JSON, or convert text with PARSE_JSON('...'). Snowflake stores VARIANT data in a columnar way internally, extracting common paths into their own columns, so queries on frequent keys are efficient.

Path syntax:

src:customer reads the top-level key customer (the colon is only for the first level).
src:customer.address.city walks nested objects with dots.
src:items[0] indexes arrays (0-based), and src['odd key'] handles names with spaces or special characters.
Key names in paths are case-sensitive, unlike SQL identifiers.

Values extracted from a VARIANT are still VARIANT. Selected strings display with double quotes, and comparisons may not behave as you expect. Cast them with ::, as in src:customer.name::STRING, src:total::NUMBER(10,2), src:ts::TIMESTAMP_TZ. Use TRY_CAST or the TRY_TO_* functions to get NULL instead of an error for bad values.

Missing paths return SQL NULL, while a JSON null value is a VARIANT null that displays as null; IS_NULL_VALUE() tells them apart. Building JSON goes the other way: OBJECT_CONSTRUCT('id', id, 'total', total), ARRAY_CONSTRUCT, ARRAY_AGG and OBJECT_AGG. TYPEOF(v) shows what a value holds.

Example

CREATE OR REPLACE TABLE raw_events (src VARIANT);

INSERT INTO raw_events
SELECT PARSE_JSON('{"id": 7, "customer": {"name": "Ana", "tier": "gold"},
                    "items": [{"sku": "A1", "qty": 2}, {"sku": "B9", "qty": 1}],
                    "total": "41.50"}');

SELECT src:customer.name           AS raw_name,     -- "Ana"  (VARIANT, quoted)
       src:customer.name::STRING   AS name,         -- Ana
       src:items[0].sku::STRING    AS first_sku,    -- A1
       src:total::NUMBER(10, 2)    AS total,        -- 41.50
       src:Customer                AS wrong_case,   -- NULL (paths are case-sensitive)
       TYPEOF(src:items)           AS t             -- ARRAY
FROM raw_events;

SELECT OBJECT_CONSTRUCT('id', 1, 'ok', TRUE);       -- { "id": 1, "ok": true }

Remember

col:key.nested[0] navigates JSON; key names are case-sensitive
Cast extracted values with :: (or TRY_CAST) before comparing or aggregating
OBJECT_CONSTRUCT and ARRAY_AGG build JSON back up

Watch out for

Comparing or joining on uncast VARIANT values and getting no matches or odd sorting.

Check yourself

What does SELECT src:Name return if the JSON key is spelled "name"?
Why write src:total::NUMBER(10,2) instead of src:total?

Official docs: docs.snowflake.com/en/user-guide/querying-semistructured

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks