I have the following schema in mongoose:

userSchema = new mongoose.Schema({
    name: {
        type: String, 
        required: true
        },
    team: {
        type: Schema.Types.ObjectId, ref: 'Team',required:true
        }
})

teamSchema = new mongoose.Schema({
    name: {
        type: String, 
        required: true
    }

   coaches: []
})

I want to join these collections, if user id is in coaches field, which is array of strings, in the team scheme.

After the join, I need to filter to get the users with a specific id in their coaches property.

For this reason, populate is not suitable here. I tried to use lookup, but can't find the right way to do this. any idea about this?

CodePudding user response：

You are using Mongoosejs and I don't think you need to do a lot, just make the proper relationship between them like

teamSchema = new mongoose.Schema({
    name: {
        type: String, 
        required: true
    }

   coaches: [{ type: Schema.Types.ObjectId, ref: 'User' }] // where User is model name 
})

and here how to use them

Team.findOne({ name: 'team1' }).populate('coaches').exec();
// or with find
Team.find().populate('coaches').exec();

Ref: https://mongoosejs.com/docs/populate.html

Update based on comment

If you need to pass query and also projection then

Team.find().populate({
    path: 'coaches',
    match: { name: 'User1' },
    select: 'name -_id' // only user name, remove _id
  }).exec();

Ref: https://mongoosejs.com/docs/populate.html#query-conditions

CodePudding user response：

• $match the user id in coaches array
• $addFields to edit the coaches array
• $map to iterate loop of coaches array
• $toObjectId to convert string type coaches id to objectId type
• $lookup with users collection

let result await Team.aggregate([
  { $match: { coaches: "5a934e000102030405000001" } },
  {
    $addFields: {
      coaches: {
        $map: {
          input: "$coaches",
          in: { $toObjectId: "$$this" }
        }
      }
    }
  },
  {
    $lookup: {
      from: "users", // update to correct users collection name
      localField: "coaches",
      foreignField: "_id",
      as: "coaches"
    }
  }
])

Playground