Aggregating data by column in array

Question

Let's say I have the following data:

gp_id | p_id
------|-------
1     | 123
2     | 432
2     | 222

For the purposes of business logic I have to transform that into this:

gp_id | p_ids
------|----------
1     | {123}
2     | {432,222}

I tried to do something like this (actually, I understand that this is the wrong approach, but still):

select gp_id, array(
    select p_id from cte
    ) as p_ids
from cte

And, predictably enough, it returns the following:

gp_id | p_ids
------|--------------
1     | {123,432,222}
2     | {123,432,222}
2     | {123,432,222}

Could anyone please help me with this?
And yes, as a matter of fact, I'm using this in a series of common table expressions.

BMN · Accepted Answer · 2017-06-22 07:39:11Z

3

I think you can use array_agg :

select
    gp_id, array_agg(p_id) as p_ids
from cte
group by gp_id

answered Jun 22, 2017 at 7:39

BMN

8,57614 gold badges51 silver badges80 bronze badges

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

1 Comment

that's absolutely great and clean solution, thank you very much!

Keyur Panchal · Accepted Answer · 2017-06-22 07:44:58Z

1

Try below query:

select c1.gp_id, array(
    select p_id from cte c2 where c2.gp_id = c1.gp_id
    ) as p_ids
from cte c1 group by c1.gp_id;

OR

select gp_id, group_concat(p_id) p_ids
from cte group by gp_id;

answered Jun 22, 2017 at 7:41

Keyur Panchal

1,4121 gold badge11 silver badges15 bronze badges

thank you, I learned a lot with your help! I had a very little practice with sql, and you showed a great way that expanded my understanding.

Sure. Welcome. Optionally you can also use group_concat.

Sorry to bother you again, but could you please give me a hint about how to put into array only distinct values?

Just add distinct before p_id

Sure buddy... :)