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)?

1 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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.