CODEWITHSUNDEEP
google-bigquery
Thadeu Melo
Thadeu Melo

Reputation: 991

Bigquery: Group query results in arrays

I have a table that lists friends from a particular user:

user_id | friend_name

1 | JOEL

1 | JACK

2 | MARIA

I want to have them grouped by user_id and each row has an array with all the friends.

How can make a selection that would do this transformation?

UPDATE:

select user_id, array_agg(friend_name) as friends from your_table group by user_id

Works fine.

However I forgot a small detail, the table has another column.

user_id | friend_name | friends_age

1 | JOEL | 21

1 | JACK | 30

2 | MARIA | 25

My solution was to add another array_agg:

select user_id, array_agg(friend_name),  array_agg(friend_age)as friends
from your_table
group by user_id

I believe it works, the only problem is when age is Null, in that case, I need to add a CASE WHEN clause.

select user_id, array_agg(friend_name),  
array_agg(CASE friend_age IS NULL THEN 0 ELSE friend_age END)as friends
from your_table
group by user_id

Upvotes: 1

Views: 4908

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

select user_id, array_agg(friend_name) as friends
from your_table
group by user_id

Upvotes: 4

Related Questions