Course contents

SnowPro Core COF-C03 Study GuidePart 6: Database objects and platformSemi-Structured Data

Semi-Structured Data

Semi-Structured Data

Definition

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.

Anatomy of raw:items[0].sku::STRING on Alice's order. raw is the VARIANT column. The colon enters its top-level key items. [0] picks the first array element, since arrays count from zero. The dot steps into the key sku. The double colon casts the VARIANT result to a SQL STRING, giving 'SNW-001'.raw:items[0].sku::STRINGRESULT'SNW-001'a STRINGVARIANT columncolon: top-level keyindex from 0dot: nested keycast to a SQL typeA path that doesn’t exist returns NULL, not an error.
  1. rawVARIANT column
  2. :itemscolon: top-level key
  3. [0]index from 0
  4. .skudot: nested key
  5. ::STRINGcast to a SQL type

RESULT'SNW-001'a STRING

A path that doesn’t exist returns NULL, not an error.

Figure 6.10 A path is read one symbol at a time, from the column to the cast. Each label names what its token does on the RAW_EVENTS document shown above.

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 }
  ]
}
LATERAL FLATTEN(input => raw:items) f
order_idcustomer_namef.indexskuqty
ORD-8842Alice Martin0SNW-0012
ORD-8842Alice Martin1SNW-2071
Figure 6.11 FLATTEN turns one document into one row per array element. Top, one row of RAW_EVENTS (prices left out); bottom, what 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 element
SELECT 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 columns
SELECT 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.

Exam trap

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.