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
In a nutshell
  • 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.

JSON filesloaded as island inVARIANT columnraw documentpath and castTyped columnsin a view or tableLATERAL FLATTENRowsone per array element

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:Name and v:name are different. Column names are not.
  • Values come back as VARIANT, so cast with ::TYPE or TO_* functions. A string value stays quoted until cast.
  • Missing paths return SQL NULL. A JSON null is a VARIANT null, which is different; STRIP_NULL_VALUE = TRUE on a file format drops JSON nulls at load, and IS_NULL_VALUE tests for them.
  • Handy functions: PARSE_JSON and TRY_PARSE_JSON turn text into VARIANT, OBJECT_CONSTRUCT and ARRAY_AGG build documents, GET and GET_PATH read values, TYPEOF shows 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;
ArgumentMeaningDefault
INPUTThe VARIANT, OBJECT or ARRAY to expandRequired
PATHPath inside the input to expandWhole input
OUTERIf TRUE, keep input rows with an empty or missing array as one row with NULLsFALSE
RECURSIVEIf TRUE, expand every nested levelFALSE
MODEOBJECT, ARRAY or BOTHBOTH

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.

Watch out

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.

Order1 rowFLATTEN itemsItem rowsn rows per orderFLATTEN optionsOption rowsn rows per item

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

ChoiceBenefitCost
Keep VARIANT onlySchema changes need no DDL; fastest to loadSlower queries on large data, no column level statistics for odd or mixed types
Typed columns extracted at loadBetter pruning, smaller storage, simple BI useSchema must be maintained
Hybrid: key fields as columns plus VARIANT for the restFast filters and flexibilitySlightly 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_NAME can load them into typed columns directly.
  • Very large documents are better split before loading, because a single VARIANT value has a size limit.
Exam tip

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 => TRUE to 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.