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.

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, defects

Each test keeps its own row. ARRAY_LENGTH returns how many defects are stored in that row.

A test with no defects shows 0.

All BigQuery lessons