Mongodb $lookup aggregation returns all documents from foreign index

Question

I have a users index

[{
 username: "foo@bar,
 roleIds: [ Types.ObjectId("1234") ]
},
{
 username: "foo@moo,
}]

With a roles

{
    "_id" : ObjectId("60465768f768621ec5828b68"),
    "name" : "admin",
    "permissionIds" : ObjectId("604657e8e715ss1f2d78b945")
}

and permissions

{
    "_id" : ObjectId("604657e8e715ss1f2d78b945"),
    "name" : "view-user",
}

When I get the user by username, I want to hydrate the role information. Not all users have roleIds so I need to be able to still return the user regardless of whether they have roleIds.

At the moment the lookup for the roles always returns every item in the roles index!

My idea is that I lookup the roles joining on the index roles by the array roleIds to _ids

Then I pipeline within that lookup to grab the permission information from within the role.

db.getCollection('users').aggregate([
        {
          $match: {
            'username':'foo@bar',
          }
      },
        {
          $lookup: {
            from: 'roles',
            let: {'roleIds': '_id'},
            as: 'roles',
            pipeline: [{
                $lookup: {
                  from: 'permissions',
                  let: {'permissionIds': "_id"},
                  as: 'permissions',
                  pipeline: [
                    {
                      $project: {
                        name: 1
                      }
                    }
                  ]
                }
              },{
                $project: {
                  name: 1,
                  permissions: 1
                }
              }
            ]
          }
        }
      ])

This route just seems to return all documents within the roles index regardless of whether it is actually a join.

Is there something I am immediately doing wrong??

Answer 1

Following things are wrong in your query:

1. You are passing wrong variables to pipeline inside let in both the lookups.
2. Missing $match stage inside pipeline which performs the actual join operation.
3. Initialize empty roleIds with [] using ifNull (since all users don't have that field).

Try this query:

db.getCollection('users').aggregate([
    {
        $match: {
            'username': 'foo@bar',
        }
    },
    {
        $lookup: {
            from: 'roles',
            let: { 'roleIds': { $ifNull: ["$roleIds", []] } },
            as: 'roles',
            pipeline: [
                {
                    $match: {
                        $expr: { $in: ["$_id", "$$roleIds"] }
                    }
                },
                {
                    $lookup: {
                        from: 'permissions',
                        let: { 'permissionId': "$permissionIds" },
                        pipeline: [
                            {
                                $match: {
                                    $expr: { $eq: ["$_id", "$$permissionId"] }
                                }
                            },
                            {
                                $project: {
                                    name: 1
                                }
                            }
                        ],
                        as: 'permissions'
                    }
                },
                {
                    $project: {
                        name: 1,
                        permissions: 1
                    }
                }
            ]
        }
    }
]);