Get objects containing max values for multiple fields using aggregation in mongodb

Question

I want to fetch the documents having highest value for a list of specifics fields. I don't know if it's possible in only one request.

Consider below data:

_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

Answer should be something like

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 },

I have tried several queries, but I can't get the whole objects containing the max values.

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

For example here I have all the max values I want but I don't get the whole matches Objects.

---- UPDATE ----

With this answer https://stackoverflow.com/a/33361913/9188650, I've made it works but I receive data in a not really user friendly way.

{
  "$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]
      ]
    }
  }
}

It will returns:

{
  "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
    }
  ]
}

My last issue are cases when multiple objects have the same max value (as the example above, 2 matches have 25 kills). I only want the oldest one in these cases.

that solution won't scale very well once your collection gets larger. — Asya Kamsky
– Asya Kamsky, Commented Feb 12, 2020 at 3:04

mickl · Accepted Answer · 2020-01-11 14:19:00Z

2

You can do it easier by using $filter and $arrayElemAt after $group stage:

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

answered Jan 11, 2020 at 14:19

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Asya Kamsky Over a year ago

this solution won't scale very well once your collection gets larger.

mickl Over a year ago

@AsyaKamsky can you suggest something more scalable ? I can modify my answer but we really don't know what OP needs are (hasn't been mentioned) so perhaps such solution works for him.

Kalane Over a year ago

I use a $match at the start of my query so my collection entries won't be really bigger than a few thousands elements. I don't know if it's already an issue ?

Asya Kamsky Over a year ago

Doing a $push of every element that passes $match filter is still going to create a big single document (unnecessarily). You could just $group to get the $max values and then do another lookup to fetch documents with those values?

Collectives™ on Stack Overflow

Get objects containing max values for multiple fields using aggregation in mongodb

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related