Postgresql: most common values in array column

Question

I have a table which looks like this:

post_id   tags
---       ----
1         {'tag1','tag2','tag3'}
2         {'foo','tag3', 'tag1'}
3         {'bar','tag3','anothertag'}
...

Tags is an array column.

Is there any way to get the most common tags (e.g. to generate a tag cloud)?

For example, how do you get the tag names sorted by popularity or the top 10 tags used? (in the above example, in order: tag3, tag1, etc.)

Is it possible to compute that efficiently (for thousands of rows)?

Answer 1

You need to unnest the array, then aggregate and group:

select u.tag, count(*)
from the_table t
  cross join unnest(t.tags) as u(tag)
group by u.tag
order by count(*) desc
limit 10;