CODEWITHSUNDEEP
google-bigquery
Ollie
Ollie

Reputation: 664

distinct rows from bigquery table with array field

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

enter image description here

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

Upvotes: 2

Views: 1840

Answers (2)

saifuddin778
saifuddin778

Reputation: 7298

Something like: 

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

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

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

Upvotes: 4

Related Questions