How to group result by array column in Postgres?

Question

I have such a table

id SERIAL,
user_id INT,
community_id INT[],

The table filled this way:

id | user_id | community_id 
1  |  1      |   {2, 4}
2  |  5      |   {2, 5} 
3  |  10     |   {2, 4}

I'd like to get COUNT of users which each community has, community_id is array cuz user can be in several community at the same time.

The query should be simple as:

SELECT community_id, COUNT(user_id) FROM tbl GROUP BY community_id

The result should be like this:

community_id  | user_count
2             |  3
4             |  2
5             |  1

I do not know how to GROUP BY array column. Can anybody help me ?

This query would be as simply as you expect, if the model was properly normalized. — user330315
– user330315, Commented Nov 2, 2015 at 9:56
You have a classical one-to-many relationship. That shouldn't be represented with an array but with two tables. en.wikipedia.org/wiki/Database_normalization#Example — user330315
– user330315, Commented Nov 2, 2015 at 10:00
Please do not advise me to use junction table to implement MANY to MANY relation. I am not developing the structure of database right now, I am trying to pull needed data from tables which already exist — Thor Samsberg
– Thor Samsberg, Commented Nov 2, 2015 at 10:00

user330315 · Accepted Answer · 2015-11-02 10:14:34Z

33

You can use unnest() to get a normalized view on the data, and the aggregate:

select community_id, count(*)
from (
  select unnest(community_id) as community_id
  from tbl 
) t
group by community_id
order by community_id;

But you should really fix your data model.

answered Nov 2, 2015 at 10:14

user330315

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

Comments

Vivek S. · Accepted Answer · 2021-12-01 06:24:48Z

2

SELECT unnest(community_id) community_id ,
       count(user_id) user_count
FROM TABLE_NAME
GROUP BY 1 --community_id = 1 and user_count = 2 (index of a column in select query)
ORDER BY 1

unnest(anyarray): Expand an array to a set of rows

i.e select unnest(ARRAY[1,2]) will give

   unnest
   ------
       1
       2

edited Dec 1, 2021 at 6:24

answered Nov 2, 2015 at 10:58

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Collectives™ on Stack Overflow

How to group result by array column in Postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related