I am working on a Postgres 9.4 project with the intarray extension enabled. We have a table that looks like this:
items
-------------------------------------
id name tag_ids
--------------------------------------
1 a car {1,4}
2 a room to rent {1}
3 a boat {1,2,4,11}
4 a wine {2}
5 emily {3}
I'd like to group the tag ids if possible. Like get a count of all the elements that have a tag_id
of '{1,2,4,11}'
tag_id count
1 3
2 2
4 2
11 1
Is this possible? I would think an intersection like this:
select * from items where tag_ids && '{1,2,4,11}'
But I need to group by the array elements inside the intersection result. If I group by tag_ids, it is just the unique value.
How would I do it?