mongodb- aggregate to get counts.

Question

I've been learning mongodb and I'm a little stuck writing a query.

I have the following collection:

{
    _id : 1,
    House: A,
    inventory: [
        {
            place: Kitchen,
            item: fridge
        },
        {
            place: Kitchen,
            item: stove
        },
        {
            place: Kitchen,
            item: TV
        },
        {
            place: Bedroom,
            item: bed
        },
        {
            place: Bedroom,
            item: TV
        }
    ]
},
{
    _id : 2,
    House: B,
    inventory: [
        {
        ....
        }
    ]
},

How would I write a query to return the count of the "places" and "items"? So the output should be like this:
{id: Kitchen, PlaceCount: 3, itemCount: 3} - 3 kitchen,3 items(fridge,stove,TV)
{id: Bedroom, PlaceCount: 2, itemCount: 2} - 2 Bedroom, 2 items(bed,TV)

I need the TV count to be counted within each place.

Answer 1

You should go with the Aggregation Pipeline, which allows you to aggregate your data in multiple steps that you provide passing an array.

Your aggregation should be:

db.your_collection.aggregate([
  {$unwind: "$inventory" },
  {$group: { _id: "$inventory.place", placeCount:{$sum : 1}, items : {$push: "$inventory.item"}}},
  {$project: { placeCount: 1, itemsCount : {$size: "$items"}}}
])

Explained:

1. $unwind producing one result for each of your inventory elements
2. $group the unwinded inventory elements by place, requiring a count (using $sum) and an array of items (still by place, using $push)
3. $project the following data of your grouped results from previous step: the placeCount and the size (using $size) of your items array, which is the itemsCount