Keep every row with LEFT JOIN UNNEST
A plain UNNEST silently drops every test whose array has nothing to show. LEFT JOIN UNNEST keeps those tests, which is what you need for questions about tests that had none.
How it works
LEFT JOIN UNNEST works like a normal LEFT JOIN, with the array as the right side. Each test is kept. The elements that pass the ON condition are its matches.
FROM fact_mot_test AS f LEFT JOIN UNNEST(f.defects) AS d ON d.is_dangerouskeeps every test and matches only its dangerous defects.- A test with no dangerous defect has no matching element, whether its array is empty or full of other defects.
- Put the condition on the element in
ON. InWHEREit would remove theNULLrows again and bring back the plainUNNESTresult. - The filter on
f.completed_datestays inWHEREand prunes partitions as before. A date filter written in theONof aLEFT JOIN UNNESTdoes not count as a partition filter, so BigQuery refuses the query. COUNTIF(d.defect_type IS NULL)can count the tests without a match, as long as every matched defect has adefect_type.
Common mistake
After a LEFT JOIN UNNEST, a row is no longer a test, because each matching defect is its own row. Count distinct test ids when you want tests.
Worked example
SELECT f.mot_test_id, d.defect_text
FROM `learn.mot.fact_mot_test` AS f
LEFT JOIN UNNEST(f.defects) AS d ON d.defect_type = 'MINOR'
WHERE f.completed_date BETWEEN '2024-06-03' AND '2024-06-04'
AND f.make = 'FORD'Every FORD test of 3 and 4 June 2024 appears. Tests with minor defects show one row per minor defect.
The ON condition picks the defects that match, and the WHERE filter only touches f.