Data Transformation · Topic 6 of 14
Semi-Structured Data - Traverse, Flatten and Rebuild
Transform JSON and other semi-structured data in Snowflake: VARIANT paths, casting, LATERAL FLATTEN, OBJECT_CONSTRUCT, ARRAY_AGG and JSON null handling.
- 5 min
- VARIANT, OBJECT and ARRAY hold semi-structured data; paths use
col:key.sub[0]and keys are case sensitive. - Always cast extracted values;
PARSE_JSONconverts text andTRY_PARSE_JSONreturns NULL on bad input. LATERAL FLATTEN(input => ...)expands arrays and objects; optionsOUTER,RECURSIVE,PATH,MODE; output includesVALUE,INDEX,KEY,PATH.
What it is
Semi-structured data such as JSON, Avro, ORC, Parquet and XML has a flexible shape with nested objects and arrays. Snowflake stores it in three types: VARIANT (any value, the usual landing column), OBJECT (key and value pairs) and ARRAY (ordered values).
A data engineer does two jobs with it: flatten nested data into relational columns and rows, and build nested data back out of relational tables.
How it works
Handling: paths and casting
Use colon notation to reach into a VARIANT, dots for nested keys and brackets for array positions.
SELECT
src:customer.name::STRING AS customer_name,
src:items[0].sku::STRING AS first_sku,
src['order_total']::NUMBER(10,2) AS total
FROM raw_orders;
- Key names are case sensitive, unlike column names.
src:Customerandsrc:customerare different. - A path returns a VARIANT, so a string displays with quotes until you cast with
::STRINGorTO_VARCHAR. Cast every field you extract. GET_PATH(src, 'customer.name')is the function form of a path;GET(src, 'key')reads one level.TYPEOF(src:x)tells you the stored type, andIS_ARRAYandIS_OBJECTtest it.PARSE_JSON(text)turns a JSON string into a VARIANT;TRY_PARSE_JSONreturns NULL on invalid input instead of failing.- A missing path returns SQL
NULL. A JSONnullis a VARIANT null, which casts to SQL NULL but is not the same value;IS_NULL_VALUEtests it andSTRIP_NULL_VALUEconverts it. - Loading a file that is one big JSON array needs the file format option
STRIP_OUTER_ARRAY = TRUE, so each element becomes a row.
Snowflake stores frequently used VARIANT paths in a columnar form, but values stored as text (such as dates inside JSON) do not benefit. For hot fields, extract them into typed columns once at load or in a transformation.
Traversing: FLATTEN
FLATTEN is a table function that turns each element of an array or object into a row. Combine it with LATERAL so it runs once per source row.
SELECT
o.src:order_id::NUMBER AS order_id,
i.value:sku::STRING AS sku,
i.value:qty::NUMBER AS qty,
i.index AS line_no
FROM raw_orders o,
LATERAL FLATTEN(input => o.src:items) i;
| Output column | Meaning |
|---|---|
VALUE | The element, still a VARIANT; read fields with value:key |
INDEX | Position in an array, NULL for objects |
KEY | Key name for objects, NULL for arrays |
PATH | Path to the element |
SEQ | Sequence number of the input row |
THIS | The array or object being flattened |
| FLATTEN option | Effect |
|---|---|
INPUT | The VARIANT, OBJECT or ARRAY to expand (required) |
PATH | Expand a nested path inside the input |
OUTER => TRUE | Keep a row, with NULLs, when the array is empty or missing; the default FALSE drops it |
RECURSIVE => TRUE | Expand all nested levels, not just the first; default FALSE |
MODE | 'OBJECT', 'ARRAY' or 'BOTH' (default) |
Nested arrays need chained laterals, where the second reads the value of the first.
FROM raw_orders o,
LATERAL FLATTEN(input => o.src:items) i,
LATERAL FLATTEN(input => i.value:tags, OUTER => TRUE) t
A plain lateral flatten silently drops source rows whose array is empty or NULL. Use OUTER => TRUE when you must keep every parent row. Flattening also multiplies rows, so aggregate carefully afterward.
To load semi-structured files straight into typed columns, use MATCH_BY_COLUMN_NAME in COPY, or INFER_SCHEMA to detect the columns.
Structured to semi-structured
Build nested output from relational rows with constructor and aggregate functions.
| Function | Result |
|---|---|
OBJECT_CONSTRUCT('id', id, 'name', name) | An OBJECT from key and value pairs |
OBJECT_CONSTRUCT(*) | An OBJECT using every column name as a key |
OBJECT_CONSTRUCT_KEEP_NULL(...) | Same, but keeps keys whose value is NULL |
ARRAY_CONSTRUCT(a, b, c) | An ARRAY from values |
ARRAY_AGG(col) | Collects values from many rows into one ARRAY |
OBJECT_AGG(key, value) | Collects rows into one OBJECT |
TO_JSON(variant) | The JSON text of a VARIANT |
TO_VARIANT(x) | Wraps a value as VARIANT |
OBJECT_CONSTRUCT omits keys with NULL values, a favorite trap. Use the KEEP_NULL version when the keys must appear.
-- one JSON document per customer, with an array of order objects
SELECT OBJECT_CONSTRUCT(
'customer_id', c.id,
'orders', ARRAY_AGG(OBJECT_CONSTRUCT('order_id', o.id, 'total', o.total))
WITHIN GROUP (ORDER BY o.id)
) AS doc
FROM customers c JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;
Other useful functions: OBJECT_INSERT and OBJECT_DELETE change an object, ARRAY_SIZE counts elements, ARRAY_CONTAINS(value::VARIANT, array) tests membership, and ARRAY_TO_STRING joins an array into text. To export, run COPY INTO @stage FROM (SELECT OBJECT_CONSTRUCT(*) ...) FILE_FORMAT = (TYPE = JSON).
Common mistakes
Forgetting to cast. Without ::STRING or ::NUMBER, values stay VARIANT and compare or display unexpectedly.
Wrong key case. src:OrderId does not find orderId.
Losing parent rows. A lateral flatten without OUTER => TRUE removes rows with empty arrays.
Expecting NULL keys in OBJECT_CONSTRUCT. They are dropped unless you use OBJECT_CONSTRUCT_KEEP_NULL.
Confusing JSON null with SQL NULL. Use IS_NULL_VALUE or STRIP_NULL_VALUE to handle the VARIANT null.
What to remember for the exam
- VARIANT, OBJECT and ARRAY hold semi-structured data; paths use
col:key.sub[0]and keys are case sensitive. - Always cast extracted values;
PARSE_JSONconverts text andTRY_PARSE_JSONreturns NULL on bad input. LATERAL FLATTEN(input => ...)expands arrays and objects; optionsOUTER,RECURSIVE,PATH,MODE; output includesVALUE,INDEX,KEY,PATH.- Nested arrays need chained FLATTEN calls.
OBJECT_CONSTRUCT,ARRAY_AGG,OBJECT_AGGandTO_JSONbuild nested output; NULL keys are dropped byOBJECT_CONSTRUCT.STRIP_OUTER_ARRAYsplits a top level JSON array into rows at load time.
Check yourself
Which function and clause expand each element of an array column into its own row?
LATERAL FLATTEN(input => column).
How do you keep orders that have an empty items array when flattening?
Add OUTER => TRUE to FLATTEN.
Which function builds a JSON object from columns and also keeps keys with NULL values?
OBJECT_CONSTRUCT_KEEP_NULL.
Topic 6 of 14 in Data Transformation
Test yourself on Data Transformation
The full Snowflake DEA-C02 question bank has 709 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.