Querying VARIANT with Dot Notation
Snowflake · Semi-Structured Data Deep Dive
Theory
A VARIANT column holds a whole JSON document, and you read fields from it with path notation. A colon goes from the column into the first level (response:order_id), dots go deeper (response:customer.address.city) and square brackets read array elements or awkward keys (response:items[0], response['customer']['name']). The result is still VARIANT, so you cast it with :: to get a normal type, for example response:customer.name::STRING.
Without the cast, strings come back with JSON double quotes, like "Pune". Field names in the path are case-sensitive, even though Snowflake column names are not, and a path that does not exist returns NULL instead of an error.
Why we use it
Path notation lets you query JSON directly where it landed, without first designing tables for every field. This makes it fast to explore new sources and to pull out only the fields you need.
When to use it
Use path notation for single fields and fixed array positions. Use FLATTEN when you need one row per array element. For heavily used fields, extract them into typed columns or a view so queries stay simple and prune well.
Dot Notation Basics
data:customer.name reaches into a nested object field, and data:items[0].sku indexes into an array element — both return VARIANT, so you typically cast the result with ::STRING or ::NUMBER.
PARSE_JSON and TRY_PARSE_JSON
PARSE_JSON converts a JSON-formatted string into an actual VARIANT value; TRY_PARSE_JSON does the same but returns NULL instead of erroring on malformed input, useful for messy source data.
Building JSON Back Up
OBJECT_CONSTRUCT and ARRAY_CONSTRUCT do the reverse, assembling VARIANT objects and arrays from relational columns — handy when exporting relational data as JSON.
How it works
- VARIANT column holds JSON
- Navigate with col:field, dots for nesting, [n] for arrays
- Does the path exist in this row? Yes: Return the value as VARIANT. No: Return NULL.
- Cast with ::STRING, ::NUMBER or ::DATE
- Use the typed value in filters, joins and output
Example
SELECT
response:order_id::STRING AS order_id,
response:customer.name::STRING AS customer_name,
response:customer.address.city::STRING AS city
FROM api_responses;
-- Bracket notation (same result)
SELECT response['customer']['name']::STRING AS name FROM api_responses;
-- Array element by index + size
SELECT response:items[0]:product::STRING AS first_item,
ARRAY_SIZE(response:items) AS item_count
FROM api_responses;
-- FLATTEN the items array into rows
SELECT response:order_id::STRING AS order_id,
f.value:product::STRING AS product, f.value:price::FLOAT AS price
FROM api_responses, LATERAL FLATTEN(input => response:items) f;Key points
- Dot and bracket notation avoid FLATTEN for single-field lookups
- Always cast VARIANT results with :: to a concrete type
- JSON keys in paths are case-sensitive
- Missing paths return NULL rather than an error
- TRY_PARSE_JSON returns NULL instead of failing on invalid JSON