google-bigquery

Array_agg containing distinct structs


I'm attempting to create an array with distinct struct as values for a column, something like so

select array_agg(distinct struct(field_a, field_b)) as c FROM tables ...

is that possible?


Solution

  • #standardSQL
    SELECT ARRAY_AGG(STRUCT(field_a, field_b)) c 
    FROM (
      SELECT DISTINCT field_a, field_b
      FROM `project.dataset.table`
    )