$first in mongodb

问题:

I have a MongoDB query like

// Get scoreboard of challenge
response.aggregate = await ScoreBoardModel.aggregate([
    { $match: { challenge_id: mongoose.Types.ObjectId(req.body.challenge_id) } },
    { $group: { _id: '$user_id', value: { $sum: '$value' } } },
]);

Which outputs like

[
  {
    "_id": "5b762887b6e3a91c60c01718",
    "value": 4300
  },
  {
    "_id": "5b8b41f10186400163d0df83",
    "value": 6800
  },
  {
    "_id": "5b762590b6e3a91c60c01713",
    "value": 2023
  }
]

But i want to perform this query something like below:

response.aggregate = await ScoreBoardModel.aggregate([
    { $match: { challenge_id: mongoose.Types.ObjectId(req.body.challenge_id) } },
    { $lookup: {
        from: 'appusers',
        let: { 'user_id': '$user_id' },
        pipeline: [{ $match: { $expr: { $eq: [ '$_id', '$$user_id' ] } } },],
        as: 'user'
    } },
    { $group: { _id: '$user_id', value: { $sum: '$value' } } },
]);

I want to $lookup in appusers to get details of each appusers. How do i do that?

回答1:

You need to use $first aggregation operator to return the first document after applying the $group stage.

ScoreBoardModel.aggregate([
  { "$match": { challenge_id: mongoose.Types.ObjectId(req.body.challenge_id) } },
  { "$lookup": {
    "from": "appusers",
    "let": { "user_id": "$user_id" },
    "pipeline": [{ "$match": { "$expr": { "$eq": [ "$_id", "$$user_id" ] } } }],
    "as": "user"
  }},
  { "$group": {
    "_id": "$user_id",
    "value": { "$sum": "$value" },
    "appusers": { "$first": "$user" },
  }}
])