Group by unique items in an array column and count records that contain each item I have a table that looks like this:

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

Thanks!
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.