Data Engineering · Topic 13 of 16
Querying Semi-Structured Data and FLATTEN
Query JSON in VARIANT columns: path notation, casting, LATERAL FLATTEN arguments and output columns, and the performance trade-offs of modeling.
- 5 min
- VARIANT, OBJECT and ARRAY hold semi-structured data; colon and dot or bracket path notation reads it; keys are case sensitive.
LATERAL FLATTEN(INPUT => ...)returns SEQ, KEY, PATH, INDEX, VALUE and THIS.- OUTER keeps empty arrays, RECURSIVE expands all levels, PATH narrows the target; OUTER and RECURSIVE default to FALSE.
What it is
Snowflake stores JSON, Avro, ORC, Parquet and XML in columns of type VARIANT, with the related types OBJECT (key and value pairs) and ARRAY. You query them with SQL using path notation, and you turn nested arrays into rows with the FLATTEN table function.
The architectural question is when to leave data in VARIANT and when to model it into typed columns. Flexibility favors VARIANT; speed, pruning and clear schemas favor typed columns.
How it works
Path notation and casting
SELECT
v:order_id::NUMBER AS order_id,
v:customer.name::STRING AS customer,
v:items[0].sku::STRING AS first_sku,
v['Ship To'].city::STRING AS city
FROM raw_orders;
- A colon starts the path from the VARIANT column. Dots or square brackets go deeper, and brackets take a number for an array position.
- Key names are case sensitive:
v:Nameandv:nameare different. Column names are not. - Values come back as VARIANT, so cast with
::TYPEorTO_*functions. A string value stays quoted until cast. - Missing paths return SQL NULL. A JSON
nullis a VARIANT null, which is different;STRIP_NULL_VALUE = TRUEon a file format drops JSON nulls at load, andIS_NULL_VALUEtests for them. - Handy functions:
PARSE_JSONandTRY_PARSE_JSONturn text into VARIANT,OBJECT_CONSTRUCTandARRAY_AGGbuild documents,GETandGET_PATHread values,TYPEOFshows the type.
FLATTEN
FLATTEN is a table function that explodes an array or object into one row per element. You nearly always call it with LATERAL, so it runs once for each input row.
SELECT o.v:order_id::NUMBER AS order_id,
i.value:sku::STRING AS sku,
i.index AS position
FROM raw_orders o,
LATERAL FLATTEN(INPUT => o.v:items) i;
| Argument | Meaning | Default |
|---|---|---|
| INPUT | The VARIANT, OBJECT or ARRAY to expand | Required |
| PATH | Path inside the input to expand | Whole input |
| OUTER | If TRUE, keep input rows with an empty or missing array as one row with NULLs | FALSE |
| RECURSIVE | If TRUE, expand every nested level | FALSE |
| MODE | OBJECT, ARRAY or BOTH | BOTH |
The output columns are SEQ, KEY, PATH, INDEX, VALUE and THIS. For an array, INDEX is the position and KEY is NULL. For an object, KEY is the name and INDEX is NULL. VALUE is the element you usually want.
Without OUTER => TRUE, an input row whose array is empty or missing disappears from the result. Totals across the join can silently lose those rows.
Nested arrays
Chain FLATTEN calls to go several levels down, or use RECURSIVE => TRUE and filter on PATH or KEY.
Each FLATTEN multiplies rows, so a wide fan out can be slow and large. Flatten only what the query needs, and filter early.
Design and performance
| Choice | Benefit | Cost |
|---|---|---|
| Keep VARIANT only | Schema changes need no DDL; fastest to load | Slower queries on large data, no column level statistics for odd or mixed types |
| Typed columns extracted at load | Better pruning, smaller storage, simple BI use | Schema must be maintained |
| Hybrid: key fields as columns plus VARIANT for the rest | Fast filters and flexibility | Slightly more modeling |
Snowflake stores consistently typed elements of a VARIANT in a columnar way and keeps statistics for them, so well structured JSON still prunes reasonably. Elements with mixed types, or values stored as strings and cast at query time, get less benefit.
- Filter on typed, promoted columns that are used in most WHERE clauses and joins.
- A view with casts over a raw VARIANT table gives BI tools a typed surface without copying data.
- The search optimization service can speed selective lookups on VARIANT columns.
- Parquet, ORC and Avro files keep their own types, and
MATCH_BY_COLUMN_NAMEcan load them into typed columns directly. - Very large documents are better split before loading, because a single VARIANT value has a size limit.
If the question is "analysts struggle with nested JSON and queries are slow", the answer is usually a curated layer that flattens and casts the fields people use, not more warehouse size.
Common mistakes
Forgetting to cast. Comparing v:id = 5 can behave differently from v:id::NUMBER = 5, and results display with quotes for strings.
Using the wrong key case. v:Customer returns NULL if the key is customer.
Dropping rows with an inner FLATTEN. Use OUTER => TRUE when empty arrays still matter.
Confusing SQL NULL and JSON null. A missing key is SQL NULL; an explicit null is a VARIANT null.
What to remember for the exam
- VARIANT, OBJECT and ARRAY hold semi-structured data; colon and dot or bracket path notation reads it; keys are case sensitive.
LATERAL FLATTEN(INPUT => ...)returns SEQ, KEY, PATH, INDEX, VALUE and THIS.- OUTER keeps empty arrays, RECURSIVE expands all levels, PATH narrows the target; OUTER and RECURSIVE default to FALSE.
- Promote frequently filtered fields to typed columns for pruning and speed.
- A flattening view or curated table is the usual fix for slow nested JSON queries.
Check yourself
A query joins orders to FLATTEN of items, and orders with no items vanish. What fixes it?
Add
OUTER => TRUEto FLATTEN so those orders return one row with NULL element values.Which FLATTEN output column holds the element itself?
VALUE. INDEX is the array position and KEY is the name for object elements.
Filters on one JSON field are slow on a very large table. What is the design answer?
Extract that field into a typed column, or use a hybrid model, so pruning and statistics apply.
Topic 13 of 16 in Data Engineering
Test yourself on Data Engineering
The full Snowflake ARA-C01 question bank has 687 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.