How to turn an array into an object with summed values?

Working with Data
#1

I have the following sample documents:

{
  _id: ObjectId("61f3882cbd56c6d86dad92d9"),
  charid: ObjectId("6140d7ca11c1853b3d42c1e6"),
  pool: 'Stamina',
  successes: 7
},
{
  _id: ObjectId("61f576a7392b0461d801254a"),
  charid: ObjectId("6140d7ca11c1853b3d42c1e6"),
  pool: 'Composure',
  successes: 1
},
{
  _id: ObjectId("61f57a0fbb252e061c2a8227"),
  charid: ObjectId("61f577e1bb252e061c2a820f"),
  pool: 'Composure',
  successes: 9
}

I need to group them all by charid and add a traits field that is an object with the sums of the pool values, like so:

{
  _id: ObjectId("6140d7ca11c1853b3d42c1e6"),
  traits: {
    Composure: 10,
    Stamina: 7
  }
}

How can I do this? I’ve looked at $mergeObjects and $addFields, but while either of those seem like they might work, I’m not exactly sure how to wrangle them into what I want.

#2

I just realized my title doesn’t make sense. My bad. Seems I can’t edit. It should be: “How to merge the sum of different documents based on one field value?”

#3

Hello, welcome : )

If you want the sums in 1 document you can try something like this

Query

  • group by pool and count
  • collect those documents in an array in key/value pairs
  • array to object(with parent traits) and replace root with it

Playmongo (put the cursor in the end of each stage to see what it does)

aggregate(
[{"$group": {"_id": "$pool", "count": {"$sum": "$successes"}}},
 {"$group": {"_id": null, "docs": {"$push": {"k": "$_id", "v": "$count"}}}},
 {"$replaceRoot": {"newRoot": {"traits": {"$arrayToObject": ["$docs"]}}}}])

This looks like the expected ouput if this is not what you need send if you can the expected output.

#4

How to you get Composure:10 for _id:“6140d7ca11c1853b3d42c1e6” in

I can see that you have one pool:Composure,successes:9 and one pool:Composure,successes:1 and 9 + 1 = 10, but none of the _id or charid of Composure:9 matches the _id of the result.

#5

@steevej Simple answer is that was a typo in my example docs. I missed fixing the last one when I posted!

@Takis This worked, thanks!

1 Like