0

I have MongoDB collection with the following format:

{
    "_id" : ObjectId("5b6b112d1bbd972848b97df4"),
    "Game" : [
            {
                    "City" : "Nizhny Novgorod",
                    "T2N" : "Costa Rica",
                    "T1N" : "Switzerland",
                    "ST" : "Nizhny Novgorod Stadium",
                    "T1S" : "2",
                    "Date" : "6/27/2018",
                    "T2S" : "2"
            },
            {
                    "City" : "Kaliningrad",
                    "T2N" : "Serbia",
                    "T1N" : "Switzerland",
                    "ST" : "Kaliningrad Stadium",
                    "T1S" : "2",
                    "Date" : "6/22/2018",
                    "T2S" : "1"
            },
            {
                    "City" : "Rostov-on-Don",
                    "T2N" : "Brazil",
                    "T1N" : "Switzerland",
                    "ST" : "Rostov Arena",
                    "T1S" : "1",
                    "Date" : "6/17/2018",
                    "T2S" : "1"
            },
            {
                    "City" : "Saint Petersburg",
                    "T2N" : "Sweden",
                    "T1N" : "Switzerland",
                    "ST" : "Saint Petersburg Stadium",
                    "T1S" : "0",
                    "Date" : "7/3/2018",
                    "T2S" : "1"
            }
    ],
    "team" : "Switzerland"
}

How can I get the total of T1S and Total of T2S for each team? I am trying the following query

db.test3.aggregate([{$match:{}},{$group:{_id:"$team", goalScored:{$sum:"Game.T1S"},goalConceaded:{$sum:"Game.T2S"}}}])

But I am getting the following output:

{ "_id" : "Tunisia", "goalScored" : 0, "goalConceaded" : 0 }
{ "_id" : "Uruguay", "goalScored" : 0, "goalConceaded" : 0 }
{ "_id" : "Egypt", "goalScored" : 0, "goalConceaded" : 0 }

In the above, the goalscored and goalconceded are always 0 which is not expected. Any help is appreciated.

Sachith Wickramaarachchi
  • 5,546
  • 6
  • 39
  • 68
noopur kharche
  • 59
  • 1
  • 11
  • Change the goal value to numeric and also don't use `$group`. Use `$project` instead. Try `db.test3.aggregate([{$match:{}},{$project:{_id:"$team", goalScored:{$sum:"Game.T1S"},goalConceaded:{$sum:"Game.T2S"}}}])` More [here](https://docs.mongodb.com/manual/reference/operator/aggregation/sum/#behavior) on $sum behavior. – s7vr Aug 10 '18 at 02:25
  • How do I change it's value to Numeric? – noopur kharche Aug 10 '18 at 02:31
  • Try link [here](https://stackoverflow.com/questions/29487351/how-to-convert-string-to-numerical-values-in-mongodb) – s7vr Aug 10 '18 at 02:32
  • thank you! that worked. I appreciate your help. – noopur kharche Aug 10 '18 at 02:53

0 Answers0