Keeping Parents with Empty Arrays

Concepts covered: Outer Joins, Array Flattening, Lateral Joins, Counting, Null Defaulting

This one usually arrives as a reconciliation. "Yesterday orders_raw had 42,000 orders and the order-lines model has 41,880. Where did 120 go?" Sometimes it hides inside a requirement instead: "line count per order, every order included." Both describe one mechanic. CROSS JOIN UNNEST is an inner join between a row and its own elements, and an array with zero elements leaves nothing to join, so the parent disappears. Order 1003, a gift-card purchase whose payload holds "items": [], sits in the input and is absent from every flattened result so far.

Three payload shapes cause the drop: an empty array, a missing items key, and "items": null. The first 2 yield zero element rows on every major engine. The third depends on the engine: Trino casts the JSON null to SQL NULL and quietly returns nothing, while Postgres stops the query with cannot extract elements from a scalar, which at least fails loudly. On Trino the gap of 120 can hold all 3 shapes, and the interviewer may ask how you would tell them apart before fixing anything.

The outer flatten

-- Trino: every order survives, empty or not
SELECT
  o.order_id,
  COUNT(i.item) AS line_count      -- counts elements, so 1003 gets 0
FROM orders_raw AS o
LEFT JOIN UNNEST(
  CAST(JSON_EXTRACT(o.payload, '$.items') AS ARRAY(JSON))
) AS i(item) ON TRUE
GROUP BY o.order_id
ORDER BY o.order_id;

LEFT JOIN ... ON TRUE keeps every order and fills the element columns with NULL when there is nothing to pair. The condition is TRUE because the lateral reference has already decided which elements belong to which row; the outer join's only job is to keep the parent that found none. The result is back to 4 rows for 4 orders, with 1003 reporting a line count of 0.

Every aggregate over 1003's element columns now sees only NULL. COUNT(i.item) returns 0, but a SUM over an element field such as qty returns NULL, because a sum over no values has no value. Wrap it in COALESCE(SUM(qty), 0) when the consumer expects a number, and say why in the room: NULL units in a revenue extract reads as missing data, while 0 reads as an order with nothing in it, which is exactly what 1003 is.

Three ways to lose the row again

Switching to the outer flatten is half the answer. The NULL placeholder row it creates for 1003 is fragile, and 3 ordinary later steps distort or delete it. The follow-ups live here.

The WHERE row is the trap interviewers set on purpose. They accept the LEFT JOIN, nod, and then ask for "line counts per order, ignoring zero-quantity lines." A predicate on an element column in WHERE runs after the join, the placeholder row fails it, and the outer flatten has become an inner one again, with no error and 120 orders missing. Moving the condition into a conditional aggregate keeps the parent and still excludes the lines.

When to skip the flatten

If the only output is a number per order, the safest way to keep 1003 is to never produce element rows at all. A scalar subquery over the array runs inside each parent row, returns exactly one value per order, and has no join that could drop anything. The COALESCE stays, because an aggregate over zero rows still returns NULL.

-- Postgres: one value per order, no join, no dropped parents
SELECT
  o.order_id,
  (SELECT COALESCE(SUM((e->>'qty')::int), 0)
     FROM jsonb_array_elements(o.payload->'items') AS e) AS units
FROM orders_raw AS o;

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.