Skip to content
Advertisement

Aggregation $match within a $sum

I was wondering if it was possible to somehow use the $match operator within the $sum function for aggregation.

{   "$unwind": "$info.avatarInfoList" },
{   "$unwind": "$info.avatarInfoList.equipList" },
{   "$unwind": "$info.avatarInfoList.equipList.flat.reliquarySubstats" },
{
    "$project": {
        "name" : "$name",
        "character" : "$info.avatarInfoList.avatarId",
        "artifact" : "$info.avatarInfoList.equipList.itemId",
        "statValue" : {
            "$sum": [
                 {"$match" : { "$info.avatarInfoList.equipList.flat.reliquarySubstats.appendPropId" : "FIGHT_PROP_CRITICAL_HURT" } },
                 {"$multiply": [2, {"$match" : { "$info.avatarInfoList.equipList.flat.reliquarySubstats.appendPropId" : "FIGHT_PROP_CRITICAL" } }]}
            ]
        },
    }
},
{ "$sort": { "statValue": -1 }},
{ '$limit' : 30 }
]).to_list(length=None) 
print(data)

I want to be able to use the value of the $sum operator within the project fields somehow, I just don’t really understand what the right approach would be for this.

Sample Input (may be too long): https://www.toptal.com/developers/hastebin/ixamekaxoq.json

Sample Output: ( 2 * FIGHT_PROP_CRITICAL ) + FIGHT_PROP_CRITICAL_HURT sorted from highest to lowest for each item.

{name: hat, character: Slayer, artifact: 13, statValue : 25.6}

Advertisement

Answer

There are still a few ambiguities about how you want to aggregate your data, but using the full document from your link, here’s one way to produce the output you want.

N.B.: Weapons in the "equipList" don’t have "reliquarySubstats" so they show a "statValue" of null in the output.

db.collection.aggregate([
  {"$unwind": "$info.avatarInfoList"},
  {"$unwind": "$info.avatarInfoList.equipList"},
  {
    "$project": {
      "_id": 0,
      "name": 1,
      "character": "$info.avatarInfoList.avatarId",
      "artifact": "$info.avatarInfoList.equipList.itemId",
      "statValue": {
        "$reduce": {
          "input": "$info.avatarInfoList.equipList.flat.reliquarySubstats",
          "initialValue": 0,
          "in": {
            "$switch": {
              "branches": [
                {
                  "case": {"$eq": ["$$this.appendPropId", "FIGHT_PROP_CRITICAL"]},
                  "then": {
                    "$add": [
                      "$$value",
                      {"$multiply": [2, "$$this.statValue"]}
                    ]
                  }
                },
                {
                  "case": {"$eq": ["$$this.appendPropId", "FIGHT_PROP_CRITICAL_HURT"]},
                  "then": {"$add": ["$$value", "$$this.statValue"]}
                }
              ],
              "default": "$$value"
            }
          }
        }
      }
    }
  },
  {"$sort": {"statValue": -1}}
])

Try it on mongoplayground.net.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement