Can you fake a join in Mongo aggregation

Question

I have documents of the form

members:
    {
    _id:ObjectId("xxx"),
    name:"John",
    branch:ObjectId("yyy")
    }

branches:
    {
    _id:ObjectId("yyy"),
    name:"Branch A"
    }

I want to report on the number of people in each branch. A straight $group pipeline does this but I can only get it to report back the branch _id and then convert it in my code. I would like to do something like:

db.members.aggregate(
    {$group:{_id:db.branches.findOne(
        {_id:"$branch"},
        {name:1}).name,count:{$sum:1}}})

Which appears not to work. Is there something along those lines that does work?

Answer 1

You can iterate over the result of the aggregation and query for the name. In the mongo shell this would look like:

db.members.aggregate(
    { $group: { _id: "$branch", count: { $sum: 1 } }
}).result.forEach(function(val, idx) {
    var branch = db.branches.findOne( { _id: val._id.$id } );
    val.name = branch.name;
    printjson(val);
 })

If you your using a language driver, do the aggregate and iterate there over the result and do a separate query for the branch name. This is a good example, why schema design is necessary in MongoDB too. I would recommend to denormalize and put the branch name directly into the member object.

Answer 2

According to the MongoDB Aggregation docs there's no direct equivalent for SQL's JOIN, which would allow aggregating data from multiple collections in a single query.