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.

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.

All BigQuery lessons