Get extra field in model summing all records from lookup document-CodePudding

Having this model:

const matchSchema = mongoose.Schema({
    location: {type: mongoose.Types.ObjectId, ref: 'Location'},
    datetime: Date,
    teamAName: String,
    teamBName: String,
    teamA: [{type: mongoose.Types.ObjectId, ref: 'Player'}],
    teamB: [{type: mongoose.Types.ObjectId, ref: 'Player'}],
    teamAScore: {type: Number, default: 0},
    teamBScore: {type: Number, default: 0},
    pichichi: [{type: mongoose.Types.ObjectId, ref: 'Player'}],
    mvp: {type: mongoose.Types.ObjectId, ref: 'Player', default:null},
});

"teamA" and "teamB" are lists containing the "_id" of every player. When retrieving a player, I want to retrieve the number of matches that he/she have played. How can I do that? Below my query just retrieving fields from model "Player"

class PlayerController {

    getAll(req, res) {
        Player.find()
            .sort('firstname')
            .exec(function(err, players) {
                res.send(players);
            });
    }
}

So, instead of just having this list:

    [
      {
        _id: new ObjectId("6232395d08663294b412d6a1"),
        firstname: 'Sam',
        lastname: 'Credington',
        __v: 0
      },
      {
        _id: new ObjectId("622479f39be8118a52af70e5"),
        firstname: 'Santi',
        lastname: 'Futsal',
        __v: 0
      },
      {
        _id: new ObjectId("6232399608663294b412d6b9"),
        firstname: 'Tom',
        lastname: 'Hendry',
        __v: 0
      }
    ]

I would like to have the amount of matches that every player played:

[
  {
    _id: new ObjectId("6232395d08663294b412d6a1"),
    firstname: 'Sam',
    lastname: 'Credington',
    matches:6,

    __v: 0
  },
  {
    _id: new ObjectId("622479f39be8118a52af70e5"),
    firstname: 'Santi',
    lastname: 'Futsal',
    matches:8,
    __v: 0
  },
  {
    _id: new ObjectId("6232399608663294b412d6b9"),
    firstname: 'Tom',
    lastname: 'Hendry',
    matches: 2,
    __v: 0
  }
]

CodePudding user response：

Here's one way you could do it.

db.players.aggregate([
  {
    "$lookup": {
      "from": "matches",
      "let": { "myId": "$_id" },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [ "$$myId", { "$setUnion": [ "$teamA", "$teamB" ] } ]
            }
          }
        },
        { "$count": "numMatches" }
      ],
      "as": "matchCount"
    }
  },
  {
    "$set": {
      "matches": {
        "$ifNull": [ { "$first": "$matchCount.numMatches" }, 0 ]
      }
    }
  },
  { "$unset": "matchCount" }
])

Try it on mongoplayground.net.