Struct fields and ARRAY_LENGTH
Several fact_mot_test columns are records or arrays, such as odometer, vehicle_snapshot and defects. Dots reach into records, and ARRAY_LENGTH counts an array without opening it.
How it works
A STRUCT is one record with named fields, such as vehicle_snapshot with make, model and fuel_type. You reach a field with a dot, the same way you reach a column through a table alias. An ARRAY is an ordered list. ARRAY_LENGTH counts its elements and keeps the test as one row.
vehicle_snapshot.makeandodometer.result_typeread one field of a record. Write the full path each time, becausemodelon its own is not a column offact_mot_test.ARRAY_LENGTH(defects)returns the number of elements and leaves one row per test. Compare withUNNEST, which gives one row per element.- A REPEATED column such as defects holds an array for every test, and the array can hold any number of elements.
ARRAY_LENGTHcounts them. - A query that reads defects reads the whole array, which is much larger than
vehicle_snapshot. Counting the defects of June 2024 reads many times the bytes of listing the makes and models.
Common mistake
A field name on its own only works when it is a column of the table. Writing model instead of vehicle_snapshot.model fails with an unrecognized name, and the same goes for result_type.
Worked example
SELECT vehicle_snapshot.fuel_type AS fuel_type,
COUNT(*) AS tests,
SUM(ARRAY_LENGTH(defects)) AS defects
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-06-03' AND '2024-06-05'
GROUP BY fuel_typeThe dotted path groups the tests by the fuel type recorded in the snapshot of each test.
SUM(ARRAY_LENGTH(defects)) adds the defect counts of the tests in each group.