Querying Nested JSON in Snowflake

Overview

Snowflake makes it straightforward to store and analyze semi-structured data—such as JSON, Avro, Parquet, and XML—alongside relational data. The most common format engineers encounter is JSON, and real-world JSON rarely arrives in a flat, row-like shape. Instead, it contains arrays, objects, and deeply nested structures that require special techniques to interrogate efficiently.

Why Snowflake Handles JSON Differently

Traditional relational databases force you to load JSON into text columns, then parse it repeatedly. Snowflake’s VARIANT data type stores the raw JSON in a column-oriented, compressed format. Snowflake automatically maintains statistics and uses optimized micro-partitions so you can query JSON with familiar SQL—no pre-shredding required.

Key Concepts

1. VARIANT Columns

All semi-structured data should be loaded into a column with the VARIANT type. Snowflake converts incoming JSON to an internal format and preserves numeric precision and data types.

2. Dot & Bracket Notation

• Dot notation (data.city) is concise but only works for object keys that follow standard identifier rules.
• Bracket notation (data['weird-key']) supports keys with spaces, hyphens, or that start with numbers.
• Array indices are zero-based: data.items[0]

3. LATERAL FLATTEN

LATERAL FLATTEN converts each element of an array or each key/value pair of an object into a separate row, allowing you to JOIN nested items back to their parent row.

4. Path Expressions

Dot and bracket notation can be chained to reach arbitrary depth (e.g., data.order.items[0].price). A path expression always returns a VARIANT, so cast it when you need a scalar:

SELECT data.order.items[0].price::NUMBER AS first_item_price
FROM sales_json;

Step-by-Step Example

Suppose we have an ecommerce_events table with a payload VARIANT column containing clickstream JSON:

{
"session_id": "ab123",
"user": {
"id": 42,
"country": "US"
},
"events": [
{"type": "page_view", "url": "/", "ts": 1716900000},
{"type": "add_to_cart", "sku": "SKU-1337", "qty": 2, "ts": 1716900023}
]
}

Extract Top-Level Fields

SELECT
payload:session_id AS session_id,
payload:user.id::INT AS user_id,
payload:user.country::STRING AS country
FROM ecommerce_events;

Flatten Event Array and Filter

SELECT
e.session_id,
evt.value:type::STRING AS event_type,
evt.value:sku::STRING AS sku,
evt.value:qty::INT AS quantity,
TO_TIMESTAMP(evt.value:ts) AS event_ts
FROM ecommerce_events AS e,
LATERAL FLATTEN(input => e.payload:events) AS evt
WHERE evt.value:type = 'add_to_cart';

This query:

1. Joins each nested events element as a row
2. Filters in SQL without touching the raw JSON again
3. Casts data types so downstream analytics stay strongly typed

Performance Best Practices

Store the Smallest JSON You Need

Loading petabytes of verbose logs wastes storage and compute. Filter or trim the payload upstream when possible.

Add Functional Clustering Keys

Snowflake’s CLUSTER BY can use JSON sub-fields: CLUSTER BY (payload:user.id). When queries frequently filter by user.id, clustering minimizes scanning.

Avoid SELECT *

Snowflake only materializes the referenced JSON paths, so limit queries to the exact paths you need.

Cast Early, Avoid Re-Parsing

Cast JSON values to NUMBER, TIMESTAMP, etc., in the first CTE. This avoids repeated casts in joins or aggregations.

Common Misconceptions

“VARIANT Is Just VARCHAR Under the Hood”

False. Snowflake uses a binary encoding that understands JSON types and supports pruning on path expressions.

“I Must Flatten Everything Before I Query”

Not always. Dot notation works on nested objects and random array indices without flattening. Flatten only when you truly need one row per nested element.

“JSON Queries Are Always Slow”

Well-designed clustering and selective path projections keep JSON queries competitive with relational ones.

Working Galaxy Example

If you use Galaxy as your SQL editor, the AI Copilot can auto-complete JSON paths once it sees sample data. Simply write:

SELECT payload: /* type "/" to browse keys */
FROM ecommerce_events
LIMIT 10;

Galaxy shows an inline explorer of payload, suggests the correct path, and even rewrites queries when the underlying JSON schema evolves.

Putting It All Together

Combining dot notation, LATERAL FLATTEN, careful casting, and Galaxy’s productivity features lets teams treat nested JSON as a first-class analytic citizen—without sacrificing the relational power of SQL.