Array subqueries, SAFE_OFFSET and ARRAY_TO_STRING

A test already carries its defects as an array. These tools work on that array inside each row, with no GROUP BY and no join back.

How it works

Take one test row at a time. An index such as defects[n] picks one element of that row’s array. ARRAY(subquery) runs a small query over that row’s array and packs the rows it returns into a new array. ARRAY_TO_STRING turns an array of text values into one text value.

Common mistake

One test whose array is too short fails the query for every row. A query that works on one day can still fail on a month.

Worked example

SELECT mot_test_id,
  defects[SAFE_ORDINAL(1)].defect_type AS first_type,
  ARRAY_TO_STRING(ARRAY(SELECT d.parsed.category FROM UNNEST(defects) AS d WHERE d.defect_type <> 'ADVISORY' ORDER BY d.parsed.category), '/') AS categories
FROM `learn.mot.fact_mot_test`
WHERE completed_date = '2024-06-03'
  AND make = 'FORD'

Every FORD test of 3 June 2024 gives one row. first_type is the type of its first defect, and NULL for a test with no defects.

categories lists the categories of the defects that are not ADVISORY, sorted, with a slash between them.

All BigQuery lessons