LATERAL FLATTEN combines rows generated from a semi-structured VARIANT value with columns from the surrounding row. FLATTEN emits one row per object, while LATERAL preserves context such as customer and order date. section 4.2
Use the construct incrementally when nested JSON arrays must become relational rows:
- Flatten the Orders array to produce one row per delivery date and access each nested object through VALUE. section 4.2
- Flatten a nested Orders by day array with a second function, aliasing each VALUE column to distinguish the levels. section 4.2
- Cast extracted fields such as dates, text, and quantities into relational column types. section 4.2
Flatten nested order arrays
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, lateral flatten(input => CO.value:"Orders by day") DO; section 4.2
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
Inspect the third-level SQL pattern and its aliases for converting nested order data into relational columns. section 4.2
