How to count every row base on array postgres

Question

I have table follow that contain user_id and follower_ids in int array:

id | user_id | follower_ids
---|---------|-------------
1  | 1       | {2,3,4}
2  | 2       | {1}
3  | 3       | {1,2}
4  | 4       | {1}

I want to get result like this

user_id | count
--------| -----
1       | 3
2       | 2
3       | 1
4       | 1

how do i run a query?

Thank you.

do you need to calculate the number of followers for each user? — Dzmitry Savinkou
– Dzmitry Savinkou, Commented Feb 12, 2016 at 5:20

Vivek S. · Accepted Answer · 2016-02-12 04:58:15Z

2

You can use array_length() function

select user_id
      ,array_length(follower_ids,1) count
from bar

answered Feb 12, 2016 at 4:58

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

Comments

Dzmitry Savinkou · Accepted Answer · 2016-02-12 05:24:32Z

0

WITH t(id,user_id,follower_ids) AS ( VALUES
  (1,1,ARRAY[2,3,4]),
  (2,2,ARRAY[1]),
  (3,3,ARRAY[1,2]),
  (3,4,ARRAY[1])
)
SELECT
  follower AS user_id,
  count(follower) AS follower_count
FROM t,unnest(t.follower_ids) AS follower
GROUP BY 1
ORDER BY 1;

Output:

 user_id | follower_count 
---------+----------------
       1 |              3
       2 |              2
       3 |              1
       4 |              1
(4 rows)

edited Feb 12, 2016 at 5:24

answered Feb 12, 2016 at 5:17

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to count every row base on array 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