CODEWITHSUNDEEP
mongodb
baldmark
baldmark

Reputation: 707

Can you fake a join in Mongo aggregation

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?

Upvotes: 5

Views: 5754

Answers (3)

BadDemiurgeException
BadDemiurgeException

Reputation: 9

I maybe have a solution:

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

Upvotes: -2

Emii Khaos
Emii Khaos

Reputation: 10085

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.

Upvotes: 7

Cristian Lupascu
Cristian Lupascu

Reputation: 40566

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.

Upvotes: 3

Related Questions