An orders table stores each order's line items as an ARRAY column inside a single row. An analyst needs one output row per line item, with the order ID repeated on each row. Which operator accomplishes this?
Show answer and explanation
Answer: B
UNNEST expands an ARRAY into one row per element. It is used in the FROM clause or joined to the parent row, so each element keeps the parent's other columns such as the order ID. ARRAY_AGG does the reverse, collapsing many rows into an array. UNPIVOT rotates a set of columns into name-value rows, which is a different reshaping, and a plain CROSS JOIN does not expand the contents of an array held inside a row.
Why the other options are wrong
- A. A cross join pairs every row with every row of another table, but it does not expand the elements of an array column held inside each row.
- C. ARRAY_AGG performs the opposite operation, collapsing many rows into a single array value, so it increases nesting rather than flattening it.
- D. UNPIVOT turns a set of columns into name-value rows. It handles wide-to-long column reshaping, not expanding the elements of an array field.
Exam tip. UNNEST flattens arrays inside a row; ARRAY_AGG goes the other way.