How to aggregate the sum of all keys in an object in mongodb

Question

Each document has a field contributions which is an object with a name and a a positive integer:

{contributions: { "jerry": 11, "jenny": 83, "jimmy": 3}}
{contributions: { "jerry": 25, "jimmy": 53}}
{contributions: { "jenny": 83, "jimmy": 3, "carlos": 9}}

I want to aggregate this (using mongo-node) such that the output reduce to the total sum per user. The format does not matter much, this would be ok:

{contributions: { "jerry": 119, "jenny": 166, "jimmy": 59, "carlos": 9}}

Or alternatively this format would also be fine:

{"user": "jerry", "total": 119}
{"user": "jenny", "total": 166}
{"user": "jimmy", "total": 59}
{"user": "carlos", "total": 9}

Can I do this with an aggregate, or map-reduce? The issue where I get stuck is that all examples seem to assume a fixed keys, but in my case the keys are dynamic.

Joe · Accepted Answer · 2022-02-10 19:57:12Z

1

Storing data in the field name is usually considered and anti-pattern with MongoDB.

Instead of

{contributions: { "jerry": 11, "jenny": 83, "jimmy": 3}}

Store it as

{contributions: [
   { name: "jerry", amount:11 }, 
   { name:"jenny", amount: 83 }, 
   { name:"jimmy", amount: 3 }
]}

This would allow index support for contributor names/amounts, and slightly simpler aggregation:

[
  {$unwind: "$contributions"},
  {$group: { _id:"$contributions.name", total: {$sum: "$contributions.amount"}}
]

If you really must keep using fieldname as data, you will likely need to convert that object to an array first, like:

[ 
  {$addFields: {contributions: {$objectToArray:"$contributions}}},
  {$unwind: "$contributions"},
  {$group: {_id: "$contributions.k", total: {$sum: "$contributions.v}}
]

answered Feb 10, 2022 at 19:57

Joe

29k4 gold badges25 silver badges50 bronze badges

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

1 Comment

Jeroen Ooms Over a year ago

Thanks. I worry that unwind will blow up to n^2 and use too much memory. I was hoping for a reducer that could sum items while iterating over the matches but I suppose that's not how mongo works.

Collectives™ on Stack Overflow

How to aggregate the sum of all keys in an object in mongodb

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related