I have a table that looks like this:

id    items[]
1     {toaster,piano}
2     {car}
3     {car, toaster}
4     {tv}
5     {toaster}

Items is an array column. I'd like to group by the different 'items' and count the number of records that contain that 'item'. So the result I'd like here would be:

item      count
toaster   3
piano     1
car       2
tv        1

Is this doable or do I really need an items table here?


  • 1
    It is certainly possible somehow, but the resulting query will be complicated and slow. This is a good example how normalization can make your queries fast. – Laurenz Albe Aug 29 '19 at 4:50
  • Please add the table definition and tag your PostgreSQL version. – McNets Aug 29 '19 at 7:06

You just need to unnest the arrays into a long form (which I do by joining against the "unnest" function call), and then re-aggregate them.

select item, count(distinct id) from items, unnest(items.items) as item group by item;

If you have to do this on a regular basis, you might want to rethink how you store the data in the first place.

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.