mongodb - 使用mongodb中的聚合获取包含多个字段最大值的对象

Question

我想为特定字段列表获取具有最高值的文档。我不知道是否可以仅在一个请求中进行。

考虑以下数据:

_id:1, kills:12,  deaths:6,   assists:1
_id:2, kills:2,   deaths:2,   assists:22
_id:3, kills:1,   deaths:2,   assists:3
_id:4, kills:0,   deaths:23,  assists:4
_id:5, kills:6,   deaths:3,   assists:5
_id:6, kills:7,   deaths:1,   assists:6

答案应该是这样的

maxKills:   { _id:1, kills:12,  deaths:6,   assists:1 },
maxDeaths:  { _id:4, kills:0,   deaths:23,  assists:4 },
maxAssists: { _id:2, kills:2,   deaths:2,   assists:22 },

我尝试了几个查询，但无法获取包含最大值的整个对象。

db.coll.aggregate([{
    $group: {
      _id: null,
      kills: { $max: "$stats.kills" },
      deaths: { $max: "$stats.deaths" },
      assists: { $max: "$stats.assists" },
    }
}])

例如在这里我有我想要的所有最大值，但我没有得到整个匹配对象。

---- 更新----

有了这个答案 https://stackoverflow.com/a/33361913/9188650 ，我已经让它工作了，但我以一种不太友好的方式接收数据。

{
  "$group": {
    "_id": null,
    "maxKills": { "$max": "$stats.kills" },
    "maxDeaths": { "$max": "$stats.deaths" },
    "maxAssists": { "$max": "$stats.assists" },
    "matches": {
      "$push": {
        "champion": "$champion",
        "gameId": "$gameId",
        "kills": "$stats.kills",
        "deaths": "$stats.deaths",
        "assists": "$stats.assists",
      }
    }
  }
},
{
  "$project": {
    "_id": 0,
    "maxKills": 1,
    "maxDeaths": 1,
    "maxAssists": 1,
    "matches": {
      "$setDifference": [
        {
          "$map": {
            "input": "$matches",
            "as": "match",
            "in": {
              $switch: {
                branches: [
                  { case: { $eq: ["$maxKills", "$$match.kills"] }, then: "$$match" },
                  { case: { $eq: ["$maxDeaths", "$$match.deaths"] }, then: "$$match" },
                  { case: { $eq: ["$maxAssists", "$$match.assists"] }, then: "$$match" },
                ],
                default: false
              }
            }
          }
        },
        [false]
      ]
    }
  }
}

它将返回:

{
  "maxKills": 25,
  "maxDeaths": 20,
  "maxAssists": 39,
  "matches": [
    {
      "champion": {
        "id": 145,
        "name": "Kai'Sa",
      },
      "gameId": 4263819967,
      "kills": 25,
      "deaths": 3,
      "assists": 16
    },
    {
      "champion": {
        "id": 8,
        "name": "Vladimir",
      },
      "gameId": 4262731529,
      "kills": 8,
      "deaths": 20,
      "assists": 3
    },
    {
      "champion": {
        "id": 22,
        "name": "Ashe",
      },
      "gameId": 4340383097,
      "kills": 9,
      "deaths": 7,
      "assists": 39
    },
    {
      "champion": {
        "id": 23,
        "name": "Tryndamere",
      },
      "gameId": 4352236936,
      "kills": 25,
      "deaths": 6,
      "assists": 22
    }
  ]
}

我的最后一个问题是多个对象具有相同最大值的情况(如上例，2 个匹配有 25 个击杀)。在这些情况下，我只想要最旧的。

Answer 1

您可以使用 $filter 更轻松地做到这一点。和 $arrayElemAt之后 $group阶段:

db.collection.aggregate([
    {
        $group: {
            _id: null,
            maxKills: { $max: "$kills" },
            maxDeaths: { $max: "$deaths" },
            maxAssists: { $max: "$assists" },
            docs: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            _id: 0,
            maxKills: { $arrayElemAt: [ { $filter: { input: "$docs", cond: { $eq: [ "$$this.kills", "$maxKills" ] } } }, 0 ] },
            maxDeaths: { $arrayElemAt: [ { $filter: { input: "$docs", cond: { $eq: [ "$$this.deaths", "$maxDeaths" ] } } }, 0 ] },
            maxAssists: { $arrayElemAt: [ { $filter: { input: "$docs", cond: { $eq: [ "$$this.assists", "$maxAssists" ] } } }, 0 ] }
        }
    }
])

Mongo Playground