Updating field by summing values

Working with Data
1

Hi,

My collection has objects that are like this:

{
    totalWins: 12,
    seasons: {
        1: {
            wins: 10
        },
        2: {
            wins: 2
        }
    }
}

I want to sum the wins from the seasons objects and put that sum into the field totalWins. However, it is possible that the seasons field might not exist, or it has no objects in itself, in which case totalWins should be 0. How would I aggregate this sum while handling these kind of special cases with efficient queries?

2

Hello : )

Storing unknown keys in documents,its not good idea i think,because we will have to find
those keys to sum them.

With arrays we don’t find them,and mongo offers so many array operations.

Why not store an array,if all seasons exist 1,2,3 … you dont even need the field season.
The index will be the key.

If there isn’t any important reason to save the seasons inside a document,you can use
this code,i used arrays.If you need documents for some reason it can work also,but more
complicated and slow i think.

Data inserts (3 documents,last one dont have a season,works also if seasons is empty array)


[
  {
    "afield": "",
    "seasons": [
      {
        "season": 1,
        "wins": 10
      },
      {
        "season": 2,
        "wins": 2
      }
    ]
  },
  {
    "afield": "",
    "seasons": [
      {
        "season": 1,
        "wins": 4
      },
      {
        "season": 2,
        "wins": 5
      }
    ]
  },
  {
    "afield": ""
  }
]

Update

{
  "update": "testcoll",
  "updates": [
    {
      "q": {},
      "u": [
        {
          "$addFields": {
            "totalwins": {
              "$cond": [
                {
                  "$ne": [
                    {
                      "$type": "$seasons"
                    },
                    "missing"
                  ]
                },
                {
                  "$reduce": {
                    "input": "$seasons",
                    "initialValue": 0,
                    "in": {
                      "$add": [
                        "$$value",
                        "$$this.wins"
                      ]
                    }
                  }
                },
                0
              ]
            }
          }
        }
      ],
      "multi": true
    }
  ]
}

Query just to see the updated and got

[
      {
        "afield": "",
        "seasons": [
          {
            "season": 1,
            "wins": 10
          },
          {
            "season": 2,
            "wins": 2
          }
        ],
        "totalwins": 12
      },
      {
        "afield": "",
        "seasons": [
          {
            "season": 1,
            "wins": 4
          },
          {
            "season": 2,
            "wins": 5
          }
        ],
        "totalwins": 9
      },
      {
        "afield": "",
        "totalwins": 0
      }
    ]

Hope it helps ,its fast ,and simple code,if not important reason for document use i think its
fine.

1 Like
3

I am having trouble implementing this operator with the MongoDB Java driver. Is there a workaround?

4

Hello : )

This is update using a pipeline,it requires MongoDB 4.2 >= , and a java driver that allows the
use of the pipeline to update function.Check the driver i guess it will support it,at least the latest driver.

I think this from the 4.1 java driver,will work,the list will be that pipeline
(the “u” part,and the filter the “q” part that here is empty)
updateMany​(Bson filter, List<? extends Bson> update, UpdateOptions updateOptions)

In worst case you can run the command from the driver like run.Command(…)
Or use the update operators,and do the update without use of pipeline.

5

By the way your code doesn’t work. The new field is not added to the documents. I tried your code with db.runCommand and also with db.collection.update:

Summary 
    db.test.update({}, [
            {
              "$addFields": {
                "totalwins": {
                  "$cond": [
                    {
                      "$ne": [
                        {
                          "$type": "$seasons"
                        },
                        "missing"
                      ]
                    },
                    {
                      "$reduce": {
                        "input": "$seasons",
                        "initialValue": 0,
                        "in": {
                          "$add": [
                            "$$value",
                            "$$this.wins"
                          ]
                        }
                      }
                    },
                    0
                  ]
                }
              }
            }
          ],
          {"multi": true})

My documents in the test collection:

[{
  "_id": {
    "$oid": "5f765b65b9e3847a36ff10cb"
  },
  "name": "Xe",
  "seasons": [
    {
      "season": 0,
      "kills": 1502,
      "wins": 100
    },
    {
      "season": -1,
      "kills": 1,
      "wins": 10
    }
  ]
},{
  "_id": {
    "$oid": "5f765b66b9e3847a36ff10cc"
  },
  "name": "Rec",
  "seasons": [
    {
      "season": 0,
      "kills": 1502,
      "wins": 90
    }
  ]
}]
6

Hello : )

I tested the code that i sended and i gave the documents in,the query,and the result.
I tested it again with your data.

[
  {
    "_id": {
      "$oid": "5f765b65b9e3847a36ff10cb"
    },
    "name": "Xe",
    "seasons": [
      {
        "season": 0,
        "kills": 1502,
        "wins": 100
      },
      {
        "season": -1,
        "kills": 1,
        "wins": 10
      }
    ]
  },
  {
    "_id": {
      "$oid": "5f765b66b9e3847a36ff10cc"
    },
    "name": "Rec",
    "seasons": [
      {
        "season": 0,
        "kills": 1502,
        "wins": 90
      }
    ]
  }
]

Became

[
  {
    "_id": {
      "oid": "5f765b65b9e3847a36ff10cb"
    },
    "name": "Xe",
    "seasons": [
      {
        "season": 0,
        "kills": 1502,
        "wins": 100
      },
      {
        "season": -1,
        "kills": 1,
        "wins": 10
      }
    ],
    "totalwins": 110
  },
  {
    "_id": {
      "oid": "5f765b66b9e3847a36ff10cc"
    },
    "name": "Rec",
    "seasons": [
      {
        "season": 0,
        "kills": 1502,
        "wins": 90
      }
    ],
    "totalwins": 90
  }
]

If the question was how to sum all the totalwins,maybe you want this.
totalwins = sum of each season array
globaltotalwins = sum of all seasons in all seasons arrays

Assumes that totalwins field exist,or you add it with the first update.

{
  "aggregate": "testcoll",
  "pipeline": [
    {
      "$group": {
        "_id": null,
        "globalTotalWins": {
          "$sum": "$totalwins"
        }
      }
    },
    {
      "$project": {
        "_id": 0
      }
    }
  ],
  "maxTimeMS": 300000,
  "cursor": {}
}

{"globalTotalWins" 200}

If still this is not what you want,if you can give data in,and how the data should become.
and we will find the query : )

7

I want “totalwins = sum of each season array”. As I mentioned before the query you gave me before doesn’t work. I ran the command in mongo shell with db.runCommand({...}) and the new field was not added. I gave you the “data in” and “how the data should become”, but the query doesn’t work.

8

Hello

I runned the code,in my driver,now i tested in shell also,its your data,just removed the dollar from “$oid” and made it “oid”.Dollar as name field even if not error,its suggested to not be used.

You gave the data in,but you didn’t give the data out,to be sure that this is what you want.
The code works,run it on your shell,but needs mongodb>=4.2 .

> use testdb
switched to db testdb
> db.testcoll.drop()
true
> db.testcoll.insert([
...   {
...     "_id": {
...       "oid": "5f765b65b9e3847a36ff10cb"
...     },
...     "name": "Xe",
...     "seasons": [
...       {
...         "season": 0,
...         "kills": 1502,
...         "wins": 100
...       },
...       {
...         "season": -1,
...         "kills": 1,
...         "wins": 10
...       }
...     ]
...   },
...   {
...     "_id": {
...       "oid": "5f765b66b9e3847a36ff10cc"
...     },
...     "name": "Rec",
...     "seasons": [
...       {
...         "season": 0,
...         "kills": 1502,
...         "wins": 90
...       }
...     ]
...   }
... ])
BulkWriteResult({
	"writeErrors" : [ ],
	"writeConcernErrors" : [ ],
	"nInserted" : 2,
	"nUpserted" : 0,
	"nMatched" : 0,
	"nModified" : 0,
	"nRemoved" : 0,
	"upserted" : [ ]
})
> db.runCommand({
...   "update": "testcoll",
...   "updates": [
...     {
...       "q": {},
...       "u": [
...         {
...           "$addFields": {
...             "totalwins": {
...               "$cond": [
...                 {
...                   "$ne": [
...                     {
...                       "$type": "$seasons"
...                     },
...                     "missing"
...                   ]
...                 },
...                 {
...                   "$reduce": {
...                     "input": "$seasons",
...                     "initialValue": 0,
...                     "in": {
...                       "$add": [
...                         "$$value",
...                         "$$this.wins"
...                       ]
...                     }
...                   }
...                 },
...                 0
...               ]
...             }
...           }
...         }
...       ],
...       "multi": true
...     }
...   ]
... })
{ "n" : 2, "nModified" : 2, "ok" : 1 }
> db.testcoll.find().pretty()
{
	"_id" : {
		"oid" : "5f765b65b9e3847a36ff10cb"
	},
	"name" : "Xe",
	"seasons" : [
		{
			"season" : 0,
			"kills" : 1502,
			"wins" : 100
		},
		{
			"season" : -1,
			"kills" : 1,
			"wins" : 10
		}
	],
	"totalwins" : 110
}
{
	"_id" : {
		"oid" : "5f765b66b9e3847a36ff10cc"
	},
	"name" : "Rec",
	"seasons" : [
		{
			"season" : 0,
			"kills" : 1502,
			"wins" : 90
		}
	],
	"totalwins" : 90
}
9

Hello @Diamond_Block,

I tried this aggregation and it returns the totalWins as you expect, in different situations (when the seasons is empty or not existing or when data exists). Note the seasons field is not an array, but it is an object.

The aggregation converts the seasons object into an array field using the $objectToArray operator. Then, iterate the array to reduce the wins to totalWins.

db.test.aggregate([
  { 
      $project: { 
          seasons: { "$objectToArray": "$seasons" } 
      } 
  },
  { 
      $project: { 
          totalWins: { 
              $reduce: { 
                  input: { $ifNull: [ "$seasons", [] ] }, 
                  initialValue: 0, 
                  in: { $add: [ "$$value", "$$this.v.wins" ] }
              }
          }
      }
  }
])

Then, you can use an Aggregation Update to update the document (NOTE: this requires MongoDB v4.2 or newer).

10

I tried to implement your code into Java using the Java MongoDB driver but the aggregation doesn’t seem to be adding the new field totalWins. I have tried using collection.aggregate, updateMany and passing the pipeline there, replacing the second project with addFields but none of them work so far. I am new to aggregation by the way so I am a bit lost here.

I would also like to ask you the same question with Takis in terms of using objects vs arrays. Thanks!

11

I was using Mongo Compass’s web shell to run your command and it didn’t work because db was pointing to my collection as well (as opposed to my database), which is why your command didn’t work when I tried it. I had to use use to explicitly specify the database. After I’ve done that, the command works as expected.

However, for me to integrate your solution to my database, I would have to refactor my seasons objects into arrays. The original reason why I decided to use objects was because it was easy for me to query the nested season object’s fields. For example if I want to sort documents by season 1 wins, I would simply sort with seasons.1.wins.

I want to ask is this practice good (using objects instead of arrays), or should I follow your advice and use arrays? Let’s say I want to get top 10 documents with the most wins (sorted) with seasons arrays, how would I do so?

12

See this post, which uses $set (aggregation) on Java with mongo-java-driver/4.0/ . You need to use this Java driver API for update with aggregation: updateMany.

13

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.