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.

Answer 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

Answer 2

There are aggregation operators like $objectToArray and $arrayToObject:

1. Convert object to array ( key-value pair)

2. Unwind to split into individual docs.

3. Again converting the value Object to array because it is again an object.

4. Unwind again

5. Group based on id and key (This will give the sum)

6. Group stage to combine inner most key/value.

7. Group stage to combine all documents.

8. 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'
           }
       }
   }]