WITH file AS (
SELECT 1 AS file_id, ARRAY<STRING>["r", "w"] AS modes
UNION ALL SELECT 2, ARRAY<STRING>["w"]
)
SELECT id,
(SELECT mode FROM UNNEST(modes) AS mode WHERE mode = 'w') IS NOT NULL AS write_mode,
(SELECT mode FROM UNNEST(modes) AS mode WHERE mode = 'r') IS NOT NULL AS read_mode
FROM UNNEST([1, 2, 3]) id
LEFT JOIN file on file.file_id = id
Expected
Actual