How to remove duplicated rows from table with arrays in BigQuery

Question

there is a table in BigQuery that has REPEATED type columns and has duplicated rows, since the table has arrays I cannot use distinct to grab only one row.

Table looks something like this:

I want to remove the duplicated rows, the output should be like this:

I didn’t find a way to come up with the above result, anyone can help?

Answer

Consider below approach

select *
from your_table t
where true
qualify 1 = row_number() over(partition by format('%t', t))

Accepted Answer

Consider below approachselect *from your_table twhere truequalify 1 = row_number() over(partition by format('%t', t))

Advertisement

Answer