Group count with MongoDB using aggregation framework

Question

Let's say my MongoDB schema looks like this:

{car_id: "...", owner_id: "..."} 

This is a many-to-many relationship. For example, the data might look like this:

+-----+----------+--------+ | _id | owner_id | car_id | +-----+----------+--------+ |   1 |        1 |      1 | |   2 |        1 |      2 | |   3 |        1 |      3 | |   4 |        2 |      1 | |   5 |        2 |      2 | |   6 |        3 |      4 | |   7 |        3 |      5 | |   8 |        3 |      6 | |   9 |        3 |      7 | |  10 |        1 |      1 | <-- not unique +-----+----------+--------+ 

I want to get the number of cars owned by each owner. In SQL, this might look like:

SELECT owner_id, COUNT(*) AS cars_owned FROM (SELECT owner_id FROM car_owners GROUP BY owner_id, car_id) AS t GROUP BY owner_id; 

In this case, the result would look like this:

+----------+------------+ | owner_id | cars_owned | +----------+------------+ |        1 |          3 | |        2 |          2 | |        3 |          4 | +----------+------------+ 

How can I accomplish this same thing using MongoDB using the aggregation framework?

Answer 1

To accommodate the potential duplicates, you need to use two $group operations:

db.test.aggregate([     { $group: {         _id: { owner_id: '$owner_id', car_id: '$car_id' }     }},     { $group: {         _id: '$_id.owner_id',         cars_owned: { $sum: 1 }     }},     { $project: {         _id: 0,         owner_id: '$_id',         cars_owned: 1     }}]     , function(err, result){         console.log(result);     } ); 

Gives a result with a format of:

[ { cars_owned: 2, owner_id: 10 },   { cars_owned: 1, owner_id: 11 } ]