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



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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2