FLATTEN converts objects within a JSON value stored as VARIANT into separate rows. Use it when an array or nested object must become relational columns rather than remain a single semistructured value. Each flattened element is exposed through a VALUE column, from which nested keys can be selected and cast. section 4.2 section 4.6
Pair FLATTEN with LATERAL when the expanded rows must retain columns from the surrounding table expression. LATERAL joins the function output to already extracted values such as customer and order date. For a standalone array result that does not need that join, FLATTEN can be used without LATERAL inside TABLE(...). section 4.2 section 7.2.2
Flatten nested JSON progressively:
- Select top-level keys into columns, retaining the array or object that needs expansion. section 4.2
- Flatten the first array with LATERAL FLATTEN(input => ...), then read its VALUE fields and cast them to target types. section 4.2
- For an array nested inside a flattened element, add a second LATERAL FLATTEN against the first alias’s VALUE field; alias both functions to distinguish their VALUE columns. section 4.2
- Materialize growing continuously ingested results when repeatedly flattening a staging table would reduce query performance; considered options include a view and target table, materialized view, or dynamic table. section 5.3.5
Expand order items into one row per item
An order row with an Items array can yield order_id, order_datetime, baked_good_type, and quantity. FLATTEN expands each item; VALUE:"Item" and VALUE:"Quantity" select fields from that item and casts produce relational types. One order can therefore produce multiple item rows. section 5.3.5 section 5.3.5
Listing 4.4 Selecting values from JSON keys at the third level of the hierarchy
select customer_orders:"Customer"::varchar as customer, customer_orders:"Order date"::date as order_date, CO.value:"Delivery date"::date as delivery_date, DO.value:"Baked good type":: varchar as baked_good_type, DO.value:"Quantity"::number as quantity from ORDERS_PARK_INN_RAW_STG, lateral flatten (input => customer_orders:"Orders") CO, #1 lateral flatten (input => CO.value:"Orders by day") DO; #2
This query shows two LATERAL FLATTEN operations: the first expands delivery dates, and the second expands each date’s orders into typed item rows. section 4.2
Flatten an API review array
Select the reviews array from a function result, pass it to TABLE(FLATTEN(...)), and extract rating, time_created, and text from VALUE. This form is appropriate when the task is only to turn the selected array into rows, not join it to a main table expression. section 7.2.2
