Semi-Structured Data
VARIANT, OBJECT, and ARRAY are Snowflake's native semi-structured types. VARIANT holds any JSON-like value, scalar, object, or array; OBJECT is a key-value map; ARRAY is an ordered list. Snowflake stores them in an optimized internal format, not as raw text, which is why compression and pruning still work.
Getting data out of a JSON document, step by step
Take one real event from IsmaFrost's RAW_EVENTS table, loaded whole into a VARIANT column named raw:
{"order_id": "ORD-8842","customer": { "name": "Alice Martin", "segment": "B2B" },"items": [{ "sku": "SNW-001", "qty": 2, "price": 40.0 },{ "sku": "SNW-207", "qty": 1, "price": 95.5 }]}
Four moves cover almost every extraction. A colon steps into an object: raw:customer. A dot keeps stepping: raw:customer.name. Square brackets index an array from zero: raw:items[0].sku. And the double-colon cast turns the VARIANT result into a typed SQL value: raw:customer.name::STRING. Brackets also work on keys: raw['customer']['name'] is the same as raw:customer.name. Skip the cast and everything comes back as VARIANT, which compares and sorts in surprising ways, so Thomas's team rule is that no path expression reaches a SELECT list or a join key without one. Paths that don't exist return NULL rather than erroring, which is forgiving in exploration and silent-data-loss in production, another reason the cast plus an IS NULL check beats optimism. A key that holds a JSON null is different: raw:phone IS NULL is FALSE, but raw:phone::STRING IS NULL is TRUE. After the cast, a JSON null, a missing key and a typo in the path all look the same. IS_NULL_VALUE(raw:phone) returns TRUE only for a JSON null.
rawVARIANT column:itemscolon: top-level key[0]index from 0.skudot: nested key::STRINGcast to a SQL type
RESULT'SNW-001'a STRING
A path that doesn’t exist returns NULL, not an error.
Amine's first real task on the team: one row per line item, not per order. That's FLATTEN's job, and the second query below is the pattern, LATERAL FLATTEN(input => raw:items) f emitting one row per array element with the element itself in f.value. FLATTEN's output also carries f.index (position in the array), f.key (for objects), and f.path, so "which line item was first" is f.index = 0, no window function required.
raw = {"order_id": "ORD-8842","customer": { "name": "Alice Martin" },"items": [{ "sku": "SNW-001", "qty": 2 },{ "sku": "SNW-207", "qty": 1 }]}
| order_ | customer_ | f. | sku | qty |
|---|---|---|---|---|
| ORD-8842 | Alice Martin | 0 | SNW-001 | 2 |
| ORD-8842 | Alice Martin | 1 | SNW-207 | 1 |
LATERAL FLATTEN(input => raw:items) f returns: the two items become two rows, each still carrying the order and customer from its parent row, with f.index giving the position and f.value:sku, f.value:qty the item fields.SELECT raw:customer. name: : STRING AS customer_ name, raw:items[0]. sku: : STRING AS first_sku FROM raw_events;-- Explode an array into one row per elementSELECT raw:order_ id: : STRING AS order_id, f. value: sku: : STRING AS sku FROM raw_events,LATERAL FLATTEN(input => raw:items) f;-- Build JSON back out of relational columnsSELECT OBJECT_CONSTRUCT('order_id', order_id, 'amount', amount) FROM orders;
LATERAL FLATTEN is the workhorse for unnesting arrays and objects into relational rows; it's a table function, so it goes in the FROM clause, after a comma, as LATERAL FLATTEN(...) or TABLE(FLATTEN(...)), to correlate its output row-by-row with the source table it's flattening. CHECK_JSON and CHECK_XML validate a document's structural well-formedness without loading it; PARSE_JSON converts a string into a VARIANT; TO_JSON serializes a VARIANT back into a JSON-formatted string.
FLATTEN written bare, with neither LATERAL nor TABLE(), is incomplete. Use the form in the example above: FROM raw_events, LATERAL FLATTEN(input => raw:items) f. Chaining a second FLATTEN on the first one's output needs LATERAL.

