Associate Data Practitioner free question 5 / 10 · Domain 2: Data Analysis and Presentation
In the orders table, the column items has the type ARRAY<STRUCT<sku STRING, qty INT64>>. Which operation expands the array so that each line item becomes its own row?
- Structuring the result rows with SELECT AS STRUCT
- Expanding it with CROSS JOIN UNNEST(items)
- Measuring its length with ARRAY_LENGTH(items)
- Aggregating the array with ARRAY_AGG(items)
Show the answer and explanation
Answer: B
Why this is correct
The "Converting elements in an array to rows in a table" section of the BigQuery "Work with arrays" page says: "For each row N in the source table, UNNEST flattens the ARRAY from row N into a set of rows containing the ARRAY elements, and then a correlated INNER JOIN or CROSS JOIN combines this new set of rows with the single row N from the source table." Because the array elements become rows, the sku and qty of each line item can be analyzed like ordinary columns.
Why the other options are wrong
"Aggregating the array with ARRAY_AGG(items)" … ARRAY_AGG is an aggregation that gathers rows into an array. It works in the opposite direction from turning each element of an array into its own row.
"Structuring the result rows with SELECT AS STRUCT" … SELECT AS STRUCT packs the selected columns into a struct. It does not expand an existing array into several line-item rows.
"Measuring its length with ARRAY_LENGTH(items)" … ARRAY_LENGTH tells you how many line items there are, but it does not pull each sku and qty out into a separate row.
Key point
After you unnest an array, each order has as many rows as it has line items. Summing a parent value such as the order total with SUM would then double count it, so aggregate at the grain you actually want.