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.

Joe · Accepted Answer · 2021-02-09 20:24:43Z

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 9, 2021 at 20:24

Joe

29k4 gold badges25 silver badges50 bronze badges

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

3 Comments

Erni Durdevic Over a year ago

Thank you for your answer Joe! I am not able to run this code in Mongodb playground. 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?

Joe Over a year ago

No, but it does assume that all keys will be numeric. The keys will be matched by name and summed.

Erni Durdevic Over a year ago

Thanks Joe. Although this solution answers my question, the documentation of $accomulator 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."

turivishal · Accepted Answer · 2021-02-09 16:39:30Z

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 9, 2021 at 16:39

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

2 Comments

Erni Durdevic Over a year ago

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 Over a year ago

Edit: I marked this answer as correct as it is the recommended solution according to the MongoDB documentation.

Collectives™ on Stack Overflow

How to aggregate array value objects by key in MongoDB

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related