Mongodb Query Join Optimization

Lets say I have 3 collections: Grandparents { _id:1, name:old foo } Parents { _id:2, grandparentId:1, name: foo } Person { _id: 3, parentId:2, name: youngfoo } How do i optimi

Solution 1:

I think you want to do something like this. I did not test this query, but this is what i would try in your place. This is only possible on 3.6 mongodb, because it supports multiple joins. The idea is to join all 3 collections. First join is Parents and Person by Parents id, and Persons "parentsId". Second join is Parents and Grandparents. Then you filter out by grandparent name and you will get a document that contains that grandparent, his son (parent), and his grandson(person). Then you just project the person.

    db.Parents.aggregate([
       {
          $lookup:{
             from:"Person",
             localField:"_id",
             foreignField:"parentId",
             as:"Person"
          }
       },
       {
          $unwind:"$Person"
       },
       {
          $lookup:{
             from:"Grandparents",
             localField:"grandparentId",
             foreignField:"_id",
             as:"Grandparents"
          }
       },
       {
          $unwind:"$Grandparents"
       },
       {$match:{Grandparents.name:"x"}},
       {$project:{Person.name:1,Person._id:1}}
}])

I think this will do the trick