Arrays and structs are columns
In a traditional database the defects of a test would sit in their own table. In BigQuery they live inside the test row, and that changes what you pay for them.
How it works
Each row of fact_mot_test carries its defects as an array of structs: one array for each test and one struct for each defect. No join is needed to reach them. You address a field inside a struct with a dot, for example d.defect_type.
UNNEST(defects) AS dturns the array into rows, one for each defect, and you read the fields asd.defect_typeord.defect_text.- Naming only
d.defect_typereads that one field of each defect, and naming thedefectscolumn reads every field. ARRAY_LENGTH(defects)returns the number of defects of one test without unnesting.defect_summaryis a plain struct of counters.defect_summary.total_countholds the number of defects found in the test.
Common mistake
After UNNEST a row is a defect instead of a test, so COUNT(*) counts defects. A test with no defects has no row at all in a comma join with UNNEST.
Worked example
SELECT vehicle_id, ARRAY_LENGTH(defects) AS defects
FROM `learn.mot.fact_mot_test`
WHERE completed_date = '2024-06-12'
ORDER BY vehicle_id, defectsEach test keeps its own row. ARRAY_LENGTH returns how many defects are stored in that row.
A test with no defects shows 0.