What you will be able to do
- Use colon, dot and bracket path notation to pull elements out of a VARIANT column, and know which parts are case-sensitive
- Use GET and GET_PATH when an irregular path rules out the shorthand syntax
- Cast VARIANT results to typed columns
- Configure FLATTEN with PATH, OUTER, RECURSIVE and MODE, and read its output columns
- Join exploded array elements back to their parent row with LATERAL FLATTEN
Key concept
Path results are VARIANT, not VARCHAR — Every value you pull out of a VARIANT with :, . or [] is itself a VARIANT. It only becomes a typed relational column once you cast it explicitly, for example with ::string.
1.Walking a VARIANT with colon, dot and bracket notation
Semi-structured data such as JSON, Avro, ORC or Parquet usually ends up in Snowflake as a single VARIANT column holding nested OBJECTs and ARRAYs. The Snowflake docs use a car_sales table whose src column holds one sale per row: a dealership, a salesperson object, and customer and vehicle arrays. Getting from that blob to rows and columns starts with path syntax. A colon separates the column name from the first-level key, so src:dealership returns each row's dealership.
After the first level you go deeper with dots: <column>:<level1>.<level2>. Bracket notation does the same thing with single-quoted keys, src['salesperson']['name'], and returns the same result. Array elements take a zero-based index, as in src:vehicle[0].make. Case matters in a way that often catches people out: the column name follows SQL rules and is case-insensitive, but JSON keys are case-sensitive. src:salesperson.name and SRC:salesperson.name are the same path, while SRC:Salesperson.Name finds nothing. Keys that aren't valid SQL identifiers, such as keys with spaces or keys that start with a digit, need double quotes: src:"company name", zipcode_info:"94987".
SELECT src:salesperson.name
FROM car_sales
ORDER BY 1;Checkpoint 1 of 7· Check yourself
The JSON key is salesperson and the column is src. Which path returns NULL instead of the salesperson's name?
The column name can be written in any case, but element names must match the JSON keys exactly. Salesperson and Name don't match the lowercase keys.
“the column name is case-insensitive but element names are case-sensitive.”Source: docs.snowflake.com
Checkpoint 2 of 7· Exam question
A pipeline loads JSON order payloads into a table with a `VARIANT` column named `payload`. An analyst runs `SELECT payload:total_amount FROM orders` and then tries to multiply the result by a tax rate, but the multiplication fails with a numeric type error even though the value looks like a plain number in the output. What is the correct fix?
Correct answer: A — Append an explicit cast such as `payload:total_amount::NUMBER` because colon and dot path operators always return VARIANT, and arithmetic requires casting that VARIANT to a concrete numeric type first.
- A. Path extraction with `:` always returns a VARIANT-typed result regardless of the underlying JSON value's apparent type, so an explicit `::NUMBER` cast is required before the value can participate in arithmetic. This is the documented behavior of colon and dot notation on semi-structured columns.
- B. This is incorrect because the column is already stored as VARIANT after the original load (which presumably already used `PARSE_JSON` or a JSON file format), so calling `PARSE_JSON` again on an already-parsed VARIANT is unnecessary and does not address the type-casting requirement causing the error.
- C. This is incorrect because `GET` extracts an element from an OBJECT or ARRAY by key or index and still returns a VARIANT value, not a native NUMBER, so the same explicit cast would still be needed afterward.
- D. This is incorrect because VARIANT columns fully support storing numeric JSON values and using them in arithmetic once cast; redesigning the schema to flatten the column is unnecessary when a simple `::NUMBER` cast solves the stated problem.
Sources1
2.GET_PATH, staged files and casting to typed columns
Path syntax is shorthand for the functions GET and GET_PATH. GET_PATH takes the whole path as a string and works like a chain of GET calls. You still need the functions because, unlike the shorthand, they can handle irregular paths or path elements. GET also takes an index you compute at query time. GET(v, ARRAY_SIZE(v)-1), for example, returns the last element of an array, whatever its length.
SELECT GET_PATH(src, 'vehicle[0].make') FROM car_sales;
SELECT src:vehicle[0].make FROM car_sales;You don't have to load a file before traversing it. With a JSON file format, a staged file can be queried in place. $1 is the parsed document, and the same path syntax applies. The file can be in an internal or an external stage.
SELECT 'The First Employee Record is '||
S.$1:root[0].employees[0].firstName||
' '||S.$1:root[0].employees[0].lastName
FROM @%customers/contacts.json.gz (file_format => 'my_json_format') as S;Because every path result is a VARIANT, turning it into a proper relational column takes a cast. The docs do it with ::string, as in value:name::string AS "Customer Name". That gives you a plain string column, without the surrounding quotes, that you can store in a typed table.
Checkpoint 3 of 7· Check yourself
When would you choose GET or GET_PATH over the shorthand src:a.b[0] path syntax?
The functions do the same job as the shorthand but also handle irregular paths. Neither form returns VARCHAR, and the shorthand works on staged files too, as the $1 example shows.
“Unlike the path syntax, these functions can handle irregular paths or path elements.”Source: docs.snowflake.com
Sources1
3.FLATTEN: exploding arrays and objects into rows
Paths get you single values. Arrays need something more, because each element should become its own row. FLATTEN is a table function that takes a VARIANT, OBJECT or ARRAY and returns one row per element. It's the main tool for turning semi-structured data into relational rows. Only INPUT is required. The other four parameters decide what gets exploded and what happens at the edges.
| Parameter | What it controls | Default |
|---|---|---|
| PATH | Path to the element inside the VARIANT that should be flattened | Zero-length string (flatten the outermost element) |
| OUTER | TRUE emits one row with NULL KEY/INDEX/VALUE for zero-row expansions. FALSE drops those input rows | FALSE |
| RECURSIVE | TRUE expands all sub-elements. FALSE expands only the element at PATH | FALSE |
| MODE | Whether to flatten 'OBJECT', 'ARRAY' or 'BOTH' | BOTH |
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('{"a":1, "b":[77,88]}'), PATH => 'b')) f;Each output row has fixed columns. SEQ identifies the input record, and it isn't guaranteed to be gap-free or ordered. KEY holds the key for object members. PATH is the element's path. INDEX is its array position, or NULL for objects. VALUE is the element itself, and you usually traverse and cast it further. THIS is the element being flattened, which helps with recursive flattening. OUTER matters most in real pipelines. With the default FALSE, a row whose array is empty or missing disappears from the result without any warning.
Checkpoint 4 of 7· Fill the gap
This query flattens an empty array but should still return one row, with NULL in KEY, INDEX and VALUE. Which parameter completes it?
SELECT * FROM TABLE(FLATTEN(INPUT => PARSE_JSON('[]'), ? => TRUE)) f;OUTER => TRUE produces exactly one row for a zero-row expansion. Without it, the empty array returns no rows.
Source: docs.snowflake.comCheckpoint 5 of 7· Match them up
Match each FLATTEN output column to what it holds
Tap a term, then the definition that fits it.
Arrays fill INDEX and objects fill KEY. VALUE carries the data you go on to traverse, and THIS shows the container being expanded.
“The index of the element, if it is an array; otherwise NULL.”Source: docs.snowflake.com
Sources2
4.LATERAL FLATTEN: keeping each element tied to its parent row
A standalone FLATTEN loses the context of the row each element came from. When FLATTEN follows a table in the FROM clause with LATERAL, it can reference that table's columns, and every exploded row keeps its parent row's values. If one car_sales row yields three rows, that row's columns are repeated three times. The customer query below puts all of this together: LATERAL FLATTEN explodes the customer array, a path walks into each value, and a cast produces typed columns.
SELECT
value:name::string as "Customer Name",
value:address::string as "Address"
FROM
car_sales
, LATERAL FLATTEN(INPUT => SRC:customer);For a single-level array, TABLE(FLATTEN(...)) and LATERAL FLATTEN(...) give the same result. The difference shows with nesting. In car_sales, the extras array sits inside each vehicle element. Reaching it takes a second FLATTEN that reads the first one's output, and that chaining only works with LATERAL.
Checkpoint 6 of 7· Check yourself
You need one row per vehicle extra, and extras is an array nested inside the vehicle array. What's the right approach?
Nested arrays need chained FLATTEN calls, and LATERAL lets each one reference the output of the previous one while keeping the parent row's columns.
“use LATERAL so that each subsequent FLATTEN can reference the output of the previous one.”Source: docs.snowflake.com
Checkpoint 7 of 7· Exam question
A `campaigns` table has a VARIANT column `details` containing `{"channels": ["email", "sms", "push"]}` for each campaign row. A data engineer needs one output row per channel value, alongside the campaign id, so downstream reporting can aggregate spend by channel. Which query accomplishes this?
Correct answer: A — `SELECT c.campaign_id, f.value::STRING FROM campaigns c, LATERAL FLATTEN(INPUT => c.details:channels) f` because `LATERAL FLATTEN` explodes each array element into its own row joined back to the source row.
- A. `LATERAL FLATTEN` is the table function designed exactly for this: it takes an ARRAY or OBJECT expression and produces one output row per element, with a lateral join preserving the other columns from the source row such as `campaign_id`.
- B. This is incorrect because bracket indexing with a fixed position like `[0]` returns only the single element at that index for every row; it does not iterate over the array and produce one row per element.
- C. This is incorrect because `ARRAY_AGG` aggregates many input rows into a single array value per group, which is the opposite operation of exploding an existing array into multiple rows.
- D. This is incorrect because casting a VARIANT array to `ARRAY` only changes its declared type; it does not change the row cardinality or expand elements into separate output rows.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.JSON element names are case-insensitive, like SQL identifiers, so src:Salesperson.Name matches the key salesperson.Why is that wrong?
Only the column name is case-insensitive. Element names must match the JSON keys exactly.
Covered in Walking a VARIANT with colon, dot and bracket notation
2.FLATTEN keeps every source row. A row with an empty or missing array still shows up with NULLs.Why is that wrong?
OUTER defaults to FALSE, and rows that can't be expanded are dropped entirely. Set OUTER => TRUE to keep them.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Insert a colon : between the VARIANT column name and any first-level element: <column>:<level1_element>.”
↩︎ Walking a VARIANT with colon, dot and bracket notation“If an element name does not conform to Snowflake SQL identifier rules”
↩︎ Walking a VARIANT with colon, dot and bracket notation“Enclose element names in single quotes.”
↩︎ Walking a VARIANT with colon, dot and bracket notation“Optionally enclose element names in double quotes: <column>:"<level1_element>"."<level2_element>"."<level3_element>".”
↩︎ Walking a VARIANT with colon, dot and bracket notation“GET_PATH is equivalent to a chain of GET functions.”
↩︎ GET_PATH, staged files and casting to typed columns“it could be located in any internal (i.e. Snowflake) or external stage”
↩︎ GET_PATH, staged files and casting to typed columns“Cast the VARIANT output to string values:”
↩︎ GET_PATH, staged files and casting to typed columns“the LATERAL modifier joins the data with any information outside of the object.”
↩︎ LATERAL FLATTEN: keeping each element tied to its parent row“Operators : and subsequent . and [] always return VARIANT values containing strings.”
↩︎ Key concept“the column name is case-insensitive but element names are case-sensitive.”
↩︎ Exam trap 1“the query output is enclosed in double quotes because the query output is VARIANT, not VARCHAR.”
↩︎ Prediction“the column name is case-insensitive but element names are case-sensitive.”
↩︎ Checkpoint“Unlike the path syntax, these functions can handle irregular paths or path elements.”
↩︎ Checkpoint - 2.
“FLATTEN can be used to convert semi-structured data to a relational representation.”
↩︎ FLATTEN: exploding arrays and objects into rows“If TRUE, exactly one row is generated for zero-row expansions (with NULL in the KEY, INDEX, and VALUE columns).”
↩︎ FLATTEN: exploding arrays and objects into rows“If TRUE, the expansion is performed for all sub-elements recursively.”
↩︎ FLATTEN: exploding arrays and objects into rows“the values in this input row are replicated to match the number of rows produced by FLATTEN.”
↩︎ LATERAL FLATTEN: keeping each element tied to its parent row“For single-level arrays, TABLE(FLATTEN(...)) and LATERAL FLATTEN(...) produce the same result.”
↩︎ LATERAL FLATTEN: keeping each element tied to its parent row“are completely omitted from the output.”
↩︎ Exam trap 2“The index of the element, if it is an array; otherwise NULL.”
↩︎ Checkpoint“use LATERAL so that each subsequent FLATTEN can reference the output of the previous one.”
↩︎ Checkpoint