1_dupes_after_unnest.sql

with filtered_table as (
  select p.*
  from prod.traceroute_atlas_prod as p, unnest(hops) as h, unnest(resultHops) as rh
  where (
    startTime > "2019-07-01" and 
    startTime < "2019-07-02"
  )
  and (
    rh.from = "170.238.232.149" or
    rh.from = "170.238.232.150" or
    rh.from = "170.238.232.145" or
    rh.from = "170.238.232.146"
  )
)
select * from filtered_table

2_de_duped.sql

with filtered_table as (
  select p.*
  from prod.traceroute_atlas_prod as p, unnest(hops) as h, unnest(resultHops) as rh
  where (
    startTime > "2019-07-01" and 
    startTime < "2019-07-02"
  )
  and (
    rh.from = "170.238.232.149" or
    rh.from = "170.238.232.150" or
    rh.from = "170.238.232.145" or
    rh.from = "170.238.232.146"
  )
)

select ANY_VALUE(filtered_table).*
from filtered_table
group by to_json_string(filtered_table)