I'm a bit confused on how to group using aggregation but still be able to extract specific values from arrays:

    db.collection.aggregate([
    {   "$unwind": f"${stat_type}" },
    {
        "$group": {
            "_id": "$userId",
            "value" : { "$max" : f"${stat_type}.stat_value" },
            "character" : f"${stat_type}.character_name", <-- how do I extract this value that matches where the $max from above is grabbed.
        }
    },
    { "$sort": { "value": -1 }},
    { '$limit' : 30 }
    ])

Sample Entries:

{
'name' : "Tony",
'userId' : 12345,
'damage_dealt' : [ "character_name" : "James", "stat_value" : 100243 ]
}
{
'name' : "Jimmy",
'userId' : 12346,
'damage_dealt' : [ "character_name" : "James", "stat_value" : 1020243 ]
}
{
'name' : "Tony",
'userId' : 12345,
'damage_dealt' : [ "character_name" : "Lebron", "stat_value" : 99900243 ]
}

A sample output for what I'm looking for is below:

[
{
'_id':12345, 
'user' : 'Tony'
'character_name' : 'Lebron', 
'stat_value' : 99900243 
}, {
'_id':12346, 
'user' : 'Jimmy'
'character_name' : 'James', 
'stat_value' : 1020243 
}
]

CodePudding user response：

You can use the $top accumulator to achieve the desired result. Like this:

db.collection.aggregate([
  {
    "$unwind": "$damage_dealt"
  },
  {
    "$group": {
      "_id": "$userId",
      "value": {
        $top: {
          output: {
            character_name: "$damage_dealt.character_name",
            stat_value: "$damage_dealt.stat_value"
          },
          sortBy: {
            "damage_dealt.stat_value": -1
          }
        }
      },
      
    }
  },
  {
    "$project": {
      character_name: "$value.character_name",
      stat_value: "$value.stat_value"
    }
  },
  {
    "$sort": {
      "stat_value": -1
    }
  },
  {
    "$limit": 30
  }
])

Playground link.

Or collects all the group elements in an array, and the max stat_value, then pick the object from the array containing the max stat_value.

 db.collection.aggregate([
  {
    "$unwind": "$damage_dealt"
  },
  {
    "$group": {
      "_id": "$userId",
      "max_stat": {
        "$max": "$damage_dealt.stat_value"
      },
      "damages": {
        "$push": {
          name: "$name",
          damage_value: "$damage_dealt"
        }
      }
    }
  },
  {
    "$project": {
      "damages": {
        "$arrayElemAt": [
          {
            "$filter": {
              "input": "$damages",
              "as": "damage",
              "cond": {
                "$eq": [
                  "$$damage.damage_value.stat_value",
                  "$max_stat"
                ]
              }
            }
          },
          0
        ]
      }
    }
  },
  {
    "$project": {
      "character_name": "$damages.damage_value.character_name",
      "stat_value": "$damages.damage_value.stat_value",
      "name": "$damages.name"
    }
  },
  {
    "$sort": {
      "stat_value": -1
    }
  },
  {
    "$limit": 30
  }
])

Playground link.