MongoDB aggregate map by key and sum value

Question

I would like to aggregate the following by key and sum the value:

{
    "_id": {
        "$oid": "60dd5e0bd0ba24343dabc626"
    },
    "s": {
        "_71vnohpi4": {
            "q": 2
        }
    }
},
{
    "_id": {
        "$oid": "609ea0b0c85ca8e54adc610c"
    },
    "s": {
        "_71vnohpi4": {
            "q": 49
        },
        "_h2121audz": {
            "q": 20
       }
    }
}

Would like to sum the value like:

{
    "_id": {
        "$oid": "newid-doesntmatter"
    },
    "s": {
        "_71vnohpi4": {
            "q": 51
        },
        "_h2121audz": {
            "q": 20
       }
    }
}

Most of the examples available are for arrays, so $unwind doesn't work in my case. And since the keys are dynamic, I cannot specify the key name.

This is what I tried, but I can't group the keys.

Ashwin R · Accepted Answer · 2021-07-02 21:48:05Z

1

There are aggregation operators like $objectToArray and $arrayToObject:

Convert object to array ( key-value pair)
Unwind to split into individual docs.
Again converting the value Object to array because it is again an object.
Unwind again
Group based on id and key (This will give the sum)
Group stage to combine inner most key/value.
Group stage to combine all documents.

Project using $arrayToObject to arrive at a single object.

[{
'$project': {
        's': {
            '$objectToArray': '$s'
        }
    }
}, {
    '$unwind': '$s'
}, {
   '$addFields': {
        's-v': {
            '$objectToArray': '$s.v'
        }
    }
}, {
    '$unwind': '$s-v'
}, {
    '$group': {
        '_id': {
            'id': '$s.k',
            'key': '$s-v.k'
        },
        'stats': {
            '$sum': '$s-v.v'
        }
    }
}, {
    '$group': {
        '_id': '$_id.id',
        'sum': {
            '$mergeObjects': {
                '$arrayToObject': [
                    [
                        [
                            '$_id.key', '$stats'
                        ]
                    ]
                ]
            }
        }
    }
}, {
    '$group': {
        '_id': '1',
        'obj': {
            '$push': {
                'k': '$_id',
                'v': '$sum'
             }
         }
     }
}, {
    '$project': {
        'final': {
            '$arrayToObject': '$obj'
        }
    }
}]

edited Jul 2, 2021 at 21:48

answered Jul 2, 2021 at 21:39

Ashwin R

7977 silver badges16 bronze badges

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

1 Comment

feco Over a year ago

I ended up using these steps from your post, and they were enough. gist.github.com/fecogc/f9fb35a640afec9269a866adb0759482

turivishal · Accepted Answer · 2021-07-03 08:24:00Z

1

$objectToArray convert s object to an array of objects in key-value format
$unwind to deconstruct s array
$group by key and count the value of q
$group by null and construct the array of key-value pair object
$arrayToObject convert above s array to object from key-value pair

db.collection.aggregate([
  { $project: { s: { $objectToArray: "$s" } } },
  { $unwind: "$s" },
  {
    $group: {
      _id: "$s.k",
      count: { $sum: "$s.v.q" }
    }
  },
  {
    $group: {
      _id: null,
      s: {
        $push: { k: "$_id", v: { q: "$count" } }
      }
    }
  },
  { $project: { s: { $arrayToObject: "$s" } } }
])

Playground

answered Jul 3, 2021 at 8:24

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

2 Comments

Ashwin R Over a year ago

This will not work if the inner must object's key isn't 'q'.

turivishal Over a year ago

OP does not mention this in the question. he asked for "_71vnohpi4" keys that are dynamic.

Collectives™ on Stack Overflow

MongoDB aggregate map by key and sum value

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related