Aggregate MongoDB query within nested arrays over multiple documents

Question

I am doing a little hobby project for friends' disc golf results.

I'm fairly new to MongoDB and I've stuck on a query. What I want to do is to return a JSON object on the form [{player: "Name", totalThrows: (sum of Totalt field), total+/-: (sum of +/- field)}].

So in short: I want to aggregate each players total throws (Total field) and total+/-, over 3 different documents in my database.

The final JSON would look like this: [{player: "Tormod", totalThrows: 148, total+/-: 24}, {player: "Martin", totalThrows: 149, total+/-: 25}, {player: "Andreas", totalThrows: 158, total+/-: 34}]

The picture below shows my document as it's saved in MongoDB. The results array consist of the results of a specific player discgolf results. There is also not a set order on which player is first in the results array in the different documents. Each document represents a new round (think of playing 3 different days).

Code:

aggregate(
        [
            {$unwind: "$results"},
            {$group: { 
                player: "$results.PlayerName",
                throws: "$results.Totalt"
            }},
            {$group: {
                _id: "$_id", 
                totalThrows: {$sum: "$results.Totalt"}
            }}
        ])

https://mongoplayground.net/p/aaKv3aaCeCi

So i've been trying alot of different stuff, most recently i've been onto something like this: [ {$unwind: "$results"}, {$group: { player: "$results.PlayerName", throws: "$results.Totalt" }}, {$group: { _id: "$_id", totalThrows: {$sum: "$results.Totalt"} }} ]) — Kjott
– Kjott, Commented Apr 8, 2021 at 0:24
Can you add your code to the question and share a MongoPlayground link with some sample data? — Arun Kumar Mohan
– Arun Kumar Mohan, Commented Apr 8, 2021 at 0:26

Arun Kumar Mohan · Accepted Answer · 2021-04-08 00:53:06Z

1

You should pass an _id field (the group by expression) to the $group stage and use $sum to sum the Totalt and +/- values. Since the values are strings, you should convert them to integers using $toInt before summing them. Finally, you can project the values based on the desired structure.

Btw, it looks like most of the numeric fields are stored as strings in the documents. I would recommend you update the documents converting them all to numbers and make sure only numbers are added to the numeric fields in the new documents.

db.collection.aggregate([
  {
    $unwind: "$results",
  },
  {
    $group: {
      _id: "$results.PlayerName",
      totalThrows: {
        $sum: {
          $toInt: "$results.Totalt",
        },
      },
      "total+/-": {
        $sum: {
          $toInt: "$results.+/-",
        },
      },
    },
  },
  {
    $project: {
      _id: 0,
      player: "$_id",
      totalThrows: "$totalThrows",
      "total+/-": "$total+/-",
    },
  },
])

MongoPlayground

edited Apr 8, 2021 at 0:53

answered Apr 8, 2021 at 0:38

Arun Kumar Mohan

11.9k3 gold badges27 silver badges47 bronze badges

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

1 Comment

Kjott Over a year ago

The god! I guess I was semi-close, thank you so much Arun!

Collectives™ on Stack Overflow

Aggregate MongoDB query within nested arrays over multiple documents

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