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.

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_type

The 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.

All BigQuery lessons