How to aggregate array value objects by key in MongoDB

Question

Given the following collection

{ "_id": 1, "items": [ { "k": "A", "v": 1 }, { "k": "B", "v": 2 } ] }
{ "_id": 2, "items": [ { "k": "A", "v": 3 }, { "k": "B", "v": 4 } ] }

How can I sum all the items having the same key k, preserving the original object format like so:

{ "items": [ { "k": "A", "v": 4 }, { "k": "B", "v": 6 } ] }

I tried using $unwind and $group, but it returns a sequence of objects instead of single item.

{ $unwind: { path: "$items" } },
{
  $group: {
    _id: "$items.k",
    v: { $sum: "$items.v" }
  }
}

I could aggregate it back into the original format, but I feel there must be a better way to do it.

score 1 · Answer 1 · answered Feb 09 '21 at 20:24

1

You could use a custom $accumulator to merge the objects the way you want:

db.collection.aggregate([
    {$project: {
            items: {
                $arrayToObject: "$items"
            }
    }},
    {$group: {
            _id: null,
            items: {
                $accumulator: {
                    init: function(){ return {}; },
                    accumulate: function(obj, doc){
                        Object.keys(doc).forEach(function(k){
                            obj[k] = (obj[k]?obj[k]:0) + doc[k];
                        })
                        return obj;
                    },
                    accumulateArgs: ["$items"],
                    merge: function(obj, doc){
                        Object.keys(doc).forEach(function(k){
                            obj[k] = (obj[k]?obj[k]:0) + doc[k];
                        })
                        return obj;
                    }
                }
            }
    }},
    {$project:{
        _id:0,
        items:{$objectToArray:"$items"}
    }}
])

answered Feb 09 '21 at 20:24

Joe

25,000
3
22
44

Thank you for your answer Joe! I am not able to run this code in Mongodb [playground](https://mongoplayground.net/p/AWvVjyeZhXt). By reading the code I understand that the keys are expected to be in the same order and position in all documents. Is my understanding correct? – Erni Durdevic Feb 10 '21 at 12:24
No, but it does assume that all keys will be numeric. The keys will be matched by name and summed. – Joe Feb 10 '21 at 19:18
Thanks Joe. Although this solution answers my question, the documentation of [$accomulator](https://docs.mongodb.com/manual/reference/operator/aggregation/accumulator/index.html#accumulator-aggregation) recommends using pipeline operators when possible. "Executing JavaScript inside of an aggregation operator may decrease performance. Only use the $accumulator operator if the provided pipeline operators cannot fulfill your application’s needs." – Erni Durdevic Feb 23 '21 at 15:26

score 0 · Accepted Answer · answered Feb 09 '21 at 16:39

0

$unwind deconstruct items array
$group by items.k and get sum of v
$group by null and reconstruct items array

db.collection.aggregate([
  { $unwind: "$items" },
  {
    $group: {
      _id: "$items.k",
      v: { $sum: "$items.v" }
    }
  },
  {
    $group: {
      _id: null,
      items: {
        $push: {
          k: "$_id",
          v: "$v"
        }
      }
    }
  }
])

Playground

answered Feb 09 '21 at 16:39

turivishal

34,368
7
36
59

1

Thank you turivishal. This also answers my question. This is the most elegant solution, although I selected the other answer as it gives more flexibility in the aggregation. – Erni Durdevic Feb 18 '21 at 08:28
1

Edit: I marked this answer as correct as it is the recommended solution according to the MongoDB documentation. – Erni Durdevic Feb 23 '21 at 15:27

How to aggregate array value objects by key in MongoDB

2 Answers2