BigQuery array UNNEST to return distinct values in array?

Question

I have a BigQuery table that includes an String array field. For some records, the array can hold duplicate string values.

Is is possible in a BigQuery UNNEST clause to filter out the duplicates so that the UNNEST only returns distinct array string values?

Answer 1

There are many ways to do this. Since you didn't specify a desired input and output, I'll arbitrarily choose one.

Usings ARRAY_AGG(DISTINCT):

WITH data AS (
  SELECT 1 id, ["a", "a", "b", "e", "a", "c", "b", "a"] strings
)


SELECT id, ARRAY_AGG(DISTINCT string) strings
FROM data, UNNEST(strings) string
GROUP BY id