How to get value and record count of elements from array column in postgres

Question

Suppose I have the following table in Postgres:

CREATE TABLE movies (
    id serial primary key,
    title text,
    actor_ids bigint[]
)

How can I get the number of movies each actor has starred in? I should be printed like this:

actor_id | count
----------------
53       | 4
92       | 13
132      | 1
1221     | 2

The actor_ids column is an array that contains the ids of the actors starring in the movie.

Right, deleted the comment. Didn't register the bit about the array. — Error_2646
– Error_2646, Commented Mar 31, 2021 at 14:50
Yeap, it's fine. Thanks for taking the time to read my question. — don_jon
– don_jon, Commented Mar 31, 2021 at 14:55

user330315 · Accepted Answer · 2021-03-31 14:49:00Z

2

You need to first unnest the array, then you can apply a group by and count

select a.actor_id, count(*)
from movies m
  cross join unnest(m.actor_ids) as a(actor_id)
group by a.actor_id
order by a.actor_id;

answered Mar 31, 2021 at 14:49

user330315

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

1 Comment

This did the job just right, Thanks a lot! I thought it would be something unnest. Have a good one!