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.

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.

All BigQuery lessons