UNNEST an array of structs
The defects of a test live inside that test as an array, so listing or counting them means opening the array first. UNNEST is how you open it.
How it works
One row of fact_mot_test is one test. Its defects column holds zero or more defect records. A comma join with UNNEST(f.defects) pairs the test with each of its defects. The output has one row per defect, and the test columns repeat on every one of them.
FROM fact_mot_test AS f, UNNEST(f.defects) AS dgives one row per array element. Read the fields of the element with a dot, as ind.defect_type.- A test with an empty defects array has no elements, so it produces no rows at all.
WITH OFFSET AS offafter theUNNESTadds the position of each element. Positions start at 0, so add 1 for a human position.is_dangerouscan beTRUEon ADVISORY and PRS items too, so it is a different test fromdefect_type = 'DANGEROUS'.- A filter on
f.completed_datestill applies to the test and prunes partitions as usual. A filter ond.is_dangerousonly removes elements from the result. - BigQuery reads only the array fields you name. A query that leaves out
d.defect_textreads far fewer bytes than one that includes it.
Common mistake
After UNNEST, a count of rows is a count of defects. A test with five defects counts five times, and a test with none does not count at all.
Worked example
SELECT f.mot_test_id, d.defect_type, d.parsed.category AS category
FROM `learn.mot.fact_mot_test` AS f, UNNEST(f.defects) AS d
WHERE f.completed_date BETWEEN '2024-06-03' AND '2024-06-04'
AND d.defect_type = 'MAJOR'Every MAJOR defect found on 3 and 4 June 2024 becomes one row. A test with two major defects shows up twice, with the same mot_test_id.
parsed is a record inside each defect, so d.parsed.category reaches two levels down with dots.