The LATERAL modifier joins rows produced by FLATTEN with columns from the surrounding relation, turning nested JSON arrays into relational rows. section 4.6
In a continuously loaded staging table, apply it to an Items array so each order-item object becomes a row while retaining order_id and order_datetime. section 5.3.5
Listing 5.3 Flattening values from keys at the second level of the hierarchy
select order_id, order_datetime, value:"Item"::varchar as baked_good_type, value:"Quantity"::number as quantity from SPEEDY_ORDERS_RAW_STG, lateral flatten (input => items);
This query expands items and casts second-level JSON values into relational columns. section 5.3.5
Flattened order items
An order containing Croissant quantity 2 and Bagel quantity 3 produces two rows with the same order identifier and timestamp, alongside each item and quantity. section 5.3.5
Consider the operational consequence when the staging table grows:
- Materialize the flattened results when repeatedly executing the transformation causes query performance to decline; available choices include a view, target table, materialized view, or dynamic table. section 5.3.5
