CODEWITHSUNDEEP
sqlpostgresql
don_jon
don_jon

Reputation: 421

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

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.

Upvotes: 0

Views: 267

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions