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?


  • Please add the table definition and tag your PostgreSQL version.
    – McNets
    Aug 29, 2019 at 7:06

2 Answers 2


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.


You can achieve this using the extremely useful GROUP BY unnest idiom in PostgreSQL. Your query would be as follows:

SELECT UNNEST("tags"), COUNT("id")
FROM "table_name"
WHERE ARRAY_LENGTH("tags", 1) > 0
GROUP BY "unnest";

Postgres automatically assigns the column name unnest to the unnested array elements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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