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
In a nutshell
  • VARIANT, OBJECT and ARRAY hold semi-structured data; paths use col:key.sub[0] and keys are case sensitive.
  • Always cast extracted values; PARSE_JSON converts text and TRY_PARSE_JSON returns NULL on bad input.
  • LATERAL FLATTEN(input => ...) expands arrays and objects; options OUTER, RECURSIVE, PATH, MODE; output includes VALUE, 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.

JSON filesload intoVARIANT columnraw documentpaths and FLATTENRelational tabletyped columnsOBJECT_CONSTRUCT, ARRAY_AGGJSON outputnested document

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:Customer and src:customer are different.
  • A path returns a VARIANT, so a string displays with quotes until you cast with ::STRING or TO_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, and IS_ARRAY and IS_OBJECT test it.
  • PARSE_JSON(text) turns a JSON string into a VARIANT; TRY_PARSE_JSON returns NULL on invalid input instead of failing.
  • A missing path returns SQL NULL. A JSON null is a VARIANT null, which casts to SQL NULL but is not the same value; IS_NULL_VALUE tests it and STRIP_NULL_VALUE converts 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.
Exam tip

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 columnMeaning
VALUEThe element, still a VARIANT; read fields with value:key
INDEXPosition in an array, NULL for objects
KEYKey name for objects, NULL for arrays
PATHPath to the element
SEQSequence number of the input row
THISThe array or object being flattened
FLATTEN optionEffect
INPUTThe VARIANT, OBJECT or ARRAY to expand (required)
PATHExpand a nested path inside the input
OUTER => TRUEKeep a row, with NULLs, when the array is empty or missing; the default FALSE drops it
RECURSIVE => TRUEExpand 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
Order row1 row with items arrayLATERAL FLATTENItem rowsone row per elementLATERAL FLATTEN on tagsTag rowsone row per tag
Watch out

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.

FunctionResult
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;
customersrowsJOIN ordersGrouped rowsGROUP BY customerARRAY_AGG of OBJECT_CONSTRUCTOne VARIANT per customernested JSONCOPY INTO stage, TYPE JSONJSON files

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_JSON converts text and TRY_PARSE_JSON returns NULL on bad input.
  • LATERAL FLATTEN(input => ...) expands arrays and objects; options OUTER, RECURSIVE, PATH, MODE; output includes VALUE, INDEX, KEY, PATH.
  • Nested arrays need chained FLATTEN calls.
  • OBJECT_CONSTRUCT, ARRAY_AGG, OBJECT_AGG and TO_JSON build nested output; NULL keys are dropped by OBJECT_CONSTRUCT.
  • STRIP_OUTER_ARRAY splits 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.