Aggregate multiple columns into array-CodePudding

Suppose I have a table like the following

user	score_1	score_2	score_3
1	100	80	100
1	80	null	80
2	95	90	65

I would like to aggregate the 3 scores columns into an array. The result will look like (the order does not matter)

user	scores
1	[100,80,100,80,null,80]
2	[95,90,65]

I know I can take union of (user, score1), (user, score2) and (user,score3) and then do array_agg on top of that. I am wondering if there is a more efficient way.

CodePudding user response：

Consider below

select user, array_agg(score) scores
from your_table
unpivot (score for col in (score_1, score_2, score_3))
group by user

if applied to sample data in your question - output is

CodePudding user response：

If for some reason preserving null is a must for you - you can use below trick - but note - the output is not an array and rather string

select user, 
  translate(format('%t', array_agg(struct(score_1, score_2, score_3))), '()', '') scores
from your_table
group by user

or even simpler

select user, 
  format('%t', array_concat_agg([score_1,score_2,score_3])) scores
from your_table
group by user

with same output for both above