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

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.

CodePudding user response：

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}}
]