How to count occurences in a list column on postgres?

Question

I've a table with the following structure:

user  |  medias
----------------------
 1    | {ps2,xbox}
 1    | {nintendo,ps2}

How do i count the occurrences of each string in an array column?

Expected result:

media    |  amount
------------------
ps2      |    2
nintendo |    1
xbox     |    1

GMB · Accepted Answer · 2020-04-08 00:14:35Z

1

You can unnest the array with a lateral join, then aggregate:

select x.media, count(*) amount
from myable t
cross join lateral unnest(t.medias) x(media)
group by x.media
order by amount desc, x.media

answered Apr 8, 2020 at 0:09

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Answer 1