distinct rows from bigquery table with array field

Question

I have a bigquery table containing a field candidate of array type. How can I query distinct rows from this table?

In this case my query should return just the first row.

Mikhail Berlyant · Accepted Answer · 2019-10-30 23:04:47Z

4

I think below is the simplest way and works for any types and length , etc.

#standardSQL
SELECT ANY_VALUE(candidate) candidate
FROM `project.dataset.table`
GROUP BY FORMAT('%T', candidate)

Previously I used to use TO_JSON_STRING() for this - but recently realized that FORMAT() fits best for most cases like this

answered Oct 30, 2019 at 23:04

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

exactly! :o) depends on case it is either %T or %t to be used for the best result :o)

saifuddin778 · Accepted Answer · 2019-10-30 23:51:54Z

0

Something like:

select split(combed, ".") as candidate from (
   select distinct array_to_string(candidate, ".") as combed 
   from `dataset.table`
)

answered Oct 30, 2019 at 23:51

saifuddin778

7,33816 gold badges54 silver badges72 bronze badges