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.
defects[OFFSET(0)]is the first element, counted from 0.defects[ORDINAL(1)]is the same element, counted from 1.- An index past the end of the array, or below the start, stops the whole query with an error.
SAFE_OFFSET(n)andSAFE_ORDINAL(n)giveNULLthere instead, and so does a field of that missing element, as indefects[SAFE_OFFSET(5)].defect_type. ARRAY(SELECT d.defect_type FROM UNNEST(defects) AS d WHERE ... ORDER BY ...)in a select list runs once for each row of the outer query and sees that row’sdefects. It filters and sorts the array of that one row.- Each row of an
ARRAYsubquery becomes one element, so the subquery selects one column.SELECT AS STRUCT a, bpacks the columns of each row into one record. Wrapped inARRAY, it gives an array of records with the fieldsaandb. Name the fields withAS. - Without an
ORDER BY, the order of the elements anARRAYsubquery returns is not defined, becauseUNNESTdoes not promise to keep the stored order. To keep it, writeUNNEST(defects) AS d WITH OFFSET AS oand sort byo. ARRAY_REVERSE(array)turns an array around.ARRAY_TO_STRING(array, delimiter)joins an array of text values with the delimiter between two elements.- A
NULLelement is left out. The optional third argument is the text to write in its place:ARRAY_TO_STRING(['A', NULL, 'C'], '-')isA-C, and with'x'as the third argument it isA-x-C.
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.