Semi-Structured Data: Intermediate

The flatten step rarely announces itself. "Top 10 SKUs by units sold this week." "Average line price across all orders." "How many orders contain a SKU from the clearance list?" None of these says array, yet each needs the items array inside every order's JSON turned into rows before an aggregate can reach it. The strong answer writes that flatten as its own named step, then builds the business logic on top of it.

UNNEST takes an array and returns a table with one row per element. Written in FROM after the parent table, it runs once for each parent row and joins its output back to that row, so every element row carries a copy of the parent's columns. The join is lateral: the function on the right reads a column from the row on the left, which is why it needs no join condition. The running example is orders_raw, 4 orders that each have a shipping_fee column and a payload whose items array holds {sku, qty, price} objects.

Four orders went in and 6 rows came out: 3 for 1001, 1 for 1002, none for 1003 and 2 for 1004. The result's row count is the sum of the array lengths, and the grain has moved from one row per order to one row per order line. shipping_fee now repeats on every line of its order, which does no harm while it is only displayed and becomes a bug the moment a later step adds it up.

The follow-up that separates people who have flattened real payloads: each order also carries a promos array, and the answer needs both. Two independent lateral joins multiply, so an order with 3 items and 2 promos becomes 6 rows, every item paired with every promo. That shape is right only when the question is about pairs, and any sum of qty over it has silently doubled for that order.

The clearance-list disguise hides a second trap. After the flatten, COUNT(*) behind a clearance filter counts lines, so an order holding 2 clearance SKUs is counted twice. Counting orders needs COUNT(DISTINCT o.order_id), or an EXISTS over the unnested array, which never changes the grain at all. The EXISTS form is the one worth naming: it can stop at the first matching element of each order and returns exactly one verdict per order by construction.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

This one shows up as a number that is slightly too small. "Revenue rebuilt from the items array is 3% under the finance ledger." "This join from events to users loses about a fifth of rows." The JSON parses fine; the same key is typed differently by different writers. Order 1004 came from an older Android build that serialises price as "19.99", while the iOS app sends 12.5. To a JSON parser those are a string and a number, and a function that expects one misreads, rejects or quietly skips the other.

Which function breaks depends on what it expects. Casting the JSON value directly, (item->'price')::numeric in Postgres, works on the number and fails the whole query on the string with cannot cast jsonb string to type numeric. Comparisons on the raw JSON value fail silently: Postgres sorts jsonb by type before value, with every Number above every String, so item->'price' > '15'::jsonb is false for "19.99". Equality has the same flaw, because the number 123 and the string "123" are different JSON values, so a join on payload->'user_id' never matches across the 2 writers.

This is the query to open with when a type question lands, before any cast is written. Every engine has the probe: jsonb_typeof in Postgres, JSON_TYPE in BigQuery, TYPEOF on a VARIANT in Snowflake. The output says which branches the conversion needs, and on a real feed the long tail is where the surprises sit: an object where a scalar was expected, a boolean, an empty string. Six rows here; across 42,000 orders a day the third type might be 14 rows, and those 14 are what the follow-up will ask about.

Text extraction is the common ground across types. ->> in Postgres, JSON_VALUE in BigQuery, JSON_EXTRACT_SCALAR in Trino and ::STRING in Snowflake all return 12.5 for the number and 19.99 for the string, both as unquoted text. Cast that text to a decimal once, with the safe variant so a bad value becomes NULL instead of failing the run, and count those NULLs so the loss is visible.

The last column answers the follow-up before it is asked. TRY_CAST turns "N/A", "12,50" and "" into NULL, and SUM skips NULLs, so a defensive cast without a counter swaps a crash for an undercount nobody notices. Report the unparsed count beside the total, or fail the job when it crosses a threshold such as 0.1% of lines. BigQuery spells the safe cast SAFE_CAST; Snowflake uses TRY_TO_DECIMAL on the text.

"How many orders used no coupon?" The quick query, WHERE JSON_EXTRACT_SCALAR(payload, '$.coupon') IS NULL, returns 3 of the 4 orders, and it is answering a different question. Order 1002 sent "coupon": null: checkout ran the coupon step and the customer applied nothing. Orders 1003 and 1004 never sent the key, because the app builds that wrote them predate the coupon feature. Scalar extraction turns both into the same SQL NULL, so "no coupon" and "we have no idea" land in one bucket.

The JSON-returning operator keeps the distinction and the text operator destroys it. Postgres's -> gives back a jsonb null for the explicit null and SQL NULL for the absent key, while ->> maps both to SQL NULL. BigQuery's JSON_VALUE, Trino's JSON_EXTRACT_SCALAR and Snowflake's ::STRING cast all behave like ->>. Once the value is text, the information is gone, so the check runs on the JSON value or on the key itself, never on the extracted string.

The same split applies to array elements. If a client posts "items": [{"sku": "A-12", "qty": 1}, null], the flatten returns 2 element rows and the second holds a JSON null. In Postgres COUNT(e) counts it, since a jsonb null is a value, while COUNT(e->>'sku') does not, so line counts disagree depending on which column is counted. Decide whether a null element is a line, then filter with jsonb_typeof(e) <> 'null' where it is not.

Snowflake turns the trap around. A VARIANT holding JSON null is a value, so payload:coupon IS NULL is false for order 1002, and a filter written to catch every no-coupon order catches only the missing keys. The BigQuery row assumes payload uses the native JSON type. Loading can also erase the difference before any query runs: STRIP_NULL_VALUES = TRUE on a Snowflake JSON file format removes every key whose value is null, turning sent_null orders into not_sent orders permanently.

Partial-update feeds are the follow-up that tests production experience. Under JSON Merge Patch (RFC 7396), {"coupon": null} means remove the coupon and an absent coupon means leave it as it was. A change-data consumer that extracts with ->> and writes the result into a target column applies a NULL in both cases, wiping fields the source never touched. The same split decides feature flags (switched off versus never configured) and survey answers (declined versus never shown).

COALESCE(payload->>'coupon', 'none') is the line that looks tidy in a dashboard and merges 2 meanings under one label. Classify into the 3 states first, then pick a default inside each state, and keep not_sent visible as its own value so the unknowns stay counted rather than relabelled.