Snowflake stores JSON in a column of type VARIANT. You query it with a path syntax, and Snowflake stores commonly used paths in an efficient columnar way, so it is much faster than parsing JSON strings.
Loading and querying
CREATE TABLE raw_events (payload VARIANT);
SELECT payload:user.id::STRING AS user_id,
payload:amount::NUMBER(10,2) AS amount
FROM raw_events
WHERE payload:event_type::STRING = 'purchase';payload:user.id navigates into the JSON, and ::STRING casts the value. A VARIANT value keeps its JSON type, so without a cast you would get a JSON-typed result, shown with quotes. If your JSON arrives as text, wrap it with PARSE_JSON.
Arrays
SELECT e.payload:order_id::NUMBER AS order_id,
i.value:sku::STRING AS sku,
i.value:qty::NUMBER AS qty
FROM raw_events e,
LATERAL FLATTEN(input => e.payload:items) i;FLATTEN returns one row per array element, with the element in a column called value. Remember that this changes the grain from event to item.
How it is stored
When data is loaded, Snowflake analyses it and extracts commonly occurring paths into separate columnar subcolumns, with their own min and max statistics. That is what makes payload:event_type filters fast and prunable. Paths that are rare or irregular stay in the general part and cost more to read.
Limits
A single VARIANT value can be up to 128 MB uncompressed (it used to be 16 MB, and the practical limit is lower because of overhead), so check the current documentation if you have very large documents.
Good practice
Keep the raw VARIANT for flexibility and replay, but extract the fields you filter and join on into typed columns in a later layer. Typed columns prune better, are easier to document and test, and make a type change visible instead of silently returning NULL.