Database Administration
postgresql query
Updated Fri, 20 May 2022 09:12:51 GMT

Postgresql: most common values in array column


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




Solution

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;