Re-Nesting After Aggregation

Concepts covered: Array Aggregation, Grouping, Join Cardinality, Sums & Averages, Array Transforms, Common Table Expressions

This part of the question comes back as a required output shape. "One row per order, with its lines as an array." "Per customer, the distinct SKUs they bought, as a list." "Correct the prices and hand me the payload back in the same form." Each one flattens, does work at the element grain, then climbs back to the parent grain with the elements gathered up. The climb is a GROUP BY on the parent key with an array aggregate. The code below reads the earlier flatten materialised as order_lines, one row per order line.

-- Trino, reading the flattened model order_lines
-- (order_id, line_no, sku, qty, shipping_fee)
WITH per_sku AS (                    -- step 1: one row per order and SKU
  SELECT order_id, sku,
         SUM(qty)     AS qty,
         MIN(line_no) AS first_line
  FROM order_lines
  GROUP BY order_id, sku
)
SELECT                               -- step 2: one row per order
  order_id,
  SUM(qty) AS units,
  ARRAY_AGG(CAST(ROW(sku, qty) AS ROW(sku VARCHAR, qty INTEGER))
            ORDER BY first_line, sku) AS lines
FROM per_sku
GROUP BY order_id;

The first step collapses repeated SKUs within an order, which real carts produce whenever a client posts one line per tap. The second folds those rows into one per order. Narrate them as "first per order and SKU, then per order": that sentence proves you know which grain each aggregate runs at, and it is exactly where a single GROUP BY order_id over the raw lines goes wrong, because a client that posted A-12 as 2 taps in order 1001 would get an array listing it twice.

Arrays come back in no fixed order

GROUP BY promises nothing about the sequence in which rows reach the aggregate, so a bare ARRAY_AGG(sku) can return [C-33, A-12, B-07] today and a different arrangement after the cluster is resized. When position matters, the ORDER BY goes inside the aggregate call, and the ordinality captured during the flatten is the only reliable record of the source order. Interviewers who have chased a flaky pipeline test ask it directly: "will that array be identical on every run?" It is identical only when the sort key is deterministic, which is why the query above adds sku as a tie-breaker.

The parent column that gets summed

Every element row carries its parent's columns, so an aggregate over a parent-level column at the element grain counts that value once per element. Here are the order_lines rows for orders 1002 and 1004, with shipping_fee copied onto each line, folded by order_id.

SELECT order_id,
  SUM(qty)          AS units,
  SUM(shipping_fee) AS shipping_summed,
  MAX(shipping_fee) AS shipping_charged
FROM order_lines
GROUP BY order_id
ORDER BY order_id;

Order 1004 paid 6.00 for shipping and the summed column reports 12.00, because it has 2 lines. Order 1002 comes out right only because it has a single line. units comes out right because qty lives at the element grain; shipping comes out wrong because it lives at the order grain. The fixes, in order of preference: take parent-level columns from the parent table and join the element aggregate to it; use MAX or ANY_VALUE when the value is constant within each group; and treat any SUM or AVG over a column coarser than the row as a bug on sight. This is the fan-out a duplicate join key causes, and naming it that way ties the flatten to a failure every interviewer already scores.

• 34.50
• 18.50

Structs, objects and NULLs by engine

That last column applies only when the aggregated value itself is NULL, as with a bare ARRAY_AGG(sku). A struct or object built from an outer flatten's placeholder row is a non-null record of nulls on every engine: a ROW of nulls in Trino, a STRUCT of nulls in BigQuery, {"sku": null} in Postgres and {} in Snowflake. No rule in the table removes it, and IGNORE NULLS passes it straight through. Exclude the placeholder before the aggregate: FILTER (WHERE sku IS NOT NULL) on Trino and Postgres, IF(sku IS NULL, NULL, STRUCT(sku, qty)) with IGNORE NULLS on BigQuery, IFF(sku IS NULL, NULL, OBJECT_CONSTRUCT(...)) on Snowflake, then apply the empty-array fallback from the next section.

Transform in place when the grain stays put

When the work is per element and the answer is still one row per order (prices to cents, cancelled lines removed, lines sorted by price), flattening and regrouping sends every element through a shuffle only to land where it started. A correlated array subquery rebuilds the array inside each row and never changes the grain. BigQuery writes it as ARRAY(SELECT ... FROM UNNEST(o.items)), Trino offers the transform and filter lambdas, and Snowflake has TRANSFORM and FILTER higher-order functions. Offering this when the interviewer asks for "the payload back" shows you weighed the cost of the regroup rather than reaching for GROUP BY out of habit.

-- BigQuery, items stored as ARRAY<STRUCT<sku STRING, qty INT64, price NUMERIC>>
SELECT
  o.order_id,
  ARRAY(
    SELECT AS STRUCT i.sku, i.qty, CAST(i.price * 100 AS INT64) AS price_cents
    FROM UNNEST(o.items) AS i WITH OFFSET AS pos
    ORDER BY pos
  ) AS items
FROM orders AS o;

No GROUP BY, no ordering risk, and order 1003 comes back holding an empty array, because the subquery runs inside its row instead of joining against it.

About this lesson

This section is part of the Semi-Structured Data: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.