Mongoose aggregate: Filtering based on Model.associated_Model.associated_Model.field returns wrong result

I have three models User, Profile, and Institution.

User

const UserSchema = new Schema({
  name: {
    type: Schema.Types.String,
  },
  profile: {
    type: Schema.Types.ObjectId,
    ref: "profiles",
  },
});

Profile

const ProfileSchema = new Schema({
  institution: {
    type: Schema.Types.ObjectId,
    ref: "institutions",
  },
});

Institution

const InstitutionSchema = new Schema({
  name: {
    type: String,
    required: true,
  },
});

I am trying to get the list of users WHERE institution = institution_name:

User.aggregate([
    {
      $lookup: {
        from: "profiles",
        let: { profiles_id: "$profile" },
        pipeline: [
          {
            $lookup: {
              from: "institutions",
              pipeline: [
                {
                  $match: { name: institution_name },
                },
              ],
              as: "institution",
            },
          },
          { $unwind: "$institution" },
        ],
        as: "profile",
      },
    },
    { $unwind: "$profile" },
    {
      $project: {
        name: "$name",
        institution: "$profile.institution.name",
      },
    },
  ]);

For some strange reason, this is return the list of all users but it replaces the institution field with the filter value I used institution_name. Any idea how to fix this?

Answers 1

  • Main mistakes in your query is that you are not performing joins based on some condition. In the first $lookup with profiles collection you are passing profile as profiles_id but not using it in the pipeline. Same is with the case of second $lookup with institutions as well.

    Try this:

    const institution_name = "Institute 1";
    
    db.users.aggregate([
        {
            $lookup: {
                from: "profiles",
                let: { profiles_id: "$profile" },
                pipeline: [
                    {
                        $match: {
                            // Join condition.
                            $expr: { $eq: ["$_id", "$$profiles_id"] }
                        }
                    },
                    {
                        $lookup: {
                            from: "institutions",
                            let: { institution_id: "$institution" },
                            pipeline: [
                                {
                                    $match: {
                                        name: institution_name,
                                        // Join condition.
                                        $expr: { $eq: ["$_id", "$$institution_id"] }
                                    }
                                }
                            ],
                            as: "institution"
                        }
                    },
                    { $unwind: "$institution" },
                ],
                as: "profile",
            }
        },
        { $unwind: "$profile" },
        {
            $project: {
                name: "$name",
                institution: "$profile.institution.name"
            }
        }
    ]);
    

    Output:

    /* 1 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
        "name" : "Dheemanth Bhat",
        "institution" : "Institute 1"
    },
    
    /* 2 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
        "name" : "Ahmed Ghrib",
        "institution" : "Institute 1"
    }
    

    Test data:

    users collection:

    /* 1 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b8"),
        "name" : "Dheemanth Bhat",
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b5")
    },
    
    /* 2 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152b9"),
        "name" : "Ahmed Ghrib",
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b6")
    },
    
    /* 3 createdAt:3/13/2021, 6:19:07 PM*/
    {
        "_id" : ObjectId("604cb4c36b2dcb17e8b152ba"),
        "name" : "Alex Rider",
        "profile" : ObjectId("604cb4b16b2dcb17e8b152b7")
    }
    

    profiles collection:

    /* 1 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b5"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b2")
    },
    
    /* 2 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b6"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b2")
    },
    
    /* 3 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b7"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b3")
    },
    
    /* 4 createdAt:3/13/2021, 6:18:49 PM*/
    {
        "_id" : ObjectId("604cb4b16b2dcb17e8b152b8"),
        "institution" : ObjectId("604cb49a6b2dcb17e8b152b4")
    }
    

    institutions collection:

    /* 1 createdAt:3/13/2021, 6:18:26 PM*/
    {
        "_id" : ObjectId("604cb49a6b2dcb17e8b152b2"),
        "name" : "Institute 1"
    },
    
    /* 2 createdAt:3/13/2021, 6:18:26 PM*/
    {
        "_id" : ObjectId("604cb49a6b2dcb17e8b152b3"),
        "name" : "Institute 2"
    },
    
    /* 3 createdAt:3/13/2021, 6:18:26 PM*/
    {
        "_id" : ObjectId("604cb49a6b2dcb17e8b152b4"),
        "name" : "Institute 3"
    }
    

Related Articles