Question 117038 in Mongodb Question posted in
The official documentation can be found here.

Simple MongoDB Aggregation

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 
  }
]
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Here’s another way you could do it.

    db.collection.aggregate([
  {
    "$group": {
      "_id": "$userId",
      "user": {"$first": "$name"},
      "damage_dealts": {"$push": "$damage_dealt"},
      "maxStat": {"$max": {"$first": "$damage_dealt.stat_value"}}
    }
  },
  {
    "$set": {
      "outChar": {
        "$first": {
          "$arrayElemAt": [
            "$damage_dealts",
            {"$indexOfArray": ["$damage_dealts.stat_value", "$maxStat"]}
          ]
        }
      }
    }
  },
  {
    "$project": {
      "user": 1,
      "character_name": "$outChar.character_name",
      "stat_value": "$outChar.stat_value"
    }
  },
  {"$sort": {"stat_value": -1}},
  {"$limit": 30}
])

    Try it on mongoplayground.net.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search