Sum document fields where the reference id is equal

59
June 14, 2021, at 2:10 PM

I have these documents with the following structure:

{
    "_id" : ObjectId("60c42bc26296623b0056515a"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 5,
    "goals_against" : 2,
    "goals_difference" : 3,
    "matches_played" : 1,
    "won_matches" : 1,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 3
}
{
    "_id" : ObjectId("60c42bc26296623b0056515b"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 2,
    "goals_against" : 5,
    "goals_difference" : -3,
    "matches_played" : 1,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 1,
    "points" : 0
}
{
    "_id" : ObjectId("60c4eb5aaa5d6523c83f59c0"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565156")
    ],
    "goals_favor" : 5,
    "goals_against" : 2,
    "goals_difference" : 3,
    "matches_played" : 1,
    "won_matches" : 1,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 3
}
{
    "_id" : ObjectId("60c4eb5aaa5d6523c83f59c1"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565156")
    ],
    "goals_favor" : 2,
    "goals_against" : 5,
    "goals_difference" : -3,
    "matches_played" : 1,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 1,
    "points" : 0
}
{
    "_id" : ObjectId("60c4eb99aa5d6523c83f59c4"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565157")
    ],
    "goals_favor" : 5,
    "goals_against" : 2,
    "goals_difference" : 3,
    "matches_played" : 1,
    "won_matches" : 1,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 3
}
{
    "_id" : ObjectId("60c4eb99aa5d6523c83f59c5"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565157")
    ],
    "goals_favor" : 2,
    "goals_against" : 5,
    "goals_difference" : -3,
    "matches_played" : 1,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 1,
    "points" : 0
}

I'm trying to get all the docs with the goals_favor, goals_against, goals_difference, match_played, won_matches, tied_matches, lost_matches, and points summed fields, where the reference IDs in the team field are the same, note that there are three documents with the following id in the team field '60c42acf6296623b00565153' and the remaining three with the id '60c42acf6296623b00565154', so I would like a structure similar to the following:

{
    "_id" : ObjectId("60c42bc26296623b0056515a"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565153")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 15,
    "goals_against" : 6,
    "goals_difference" : 9,
    "matches_played" : 3,
    "won_matches" : 3,
    "tied_matches" : 0,
    "lost_matches" : 0,
    "points" : 9
}
{
    "_id" : ObjectId("60c42bc26296623b0056515b"),
    "team" : [ 
        ObjectId("60c42acf6296623b00565154")
    ],
    "matchday" : [ 
        ObjectId("60c42b386296623b00565155")
    ],
    "goals_favor" : 6,
    "goals_against" : 15,
    "goals_difference" : -9,
    "matches_played" : 3,
    "won_matches" : 0,
    "tied_matches" : 0,
    "lost_matches" : 3,
    "points" : 0
}

I have tried this but cannot follow:

const ids = ['60c42acf6296623b00565153', '60c42acf6296623b00565154']
TeamsDetails.aggregate([
                        { $match: { "team": { $in: ids } } },
                        { $group: { _id: "$_id", total: { $sum: "$points" } } },
                    ]).exec((error, find) => {
                        //Do something
                    })

Thank you.

Answer 1

You need to use $unwind to deconstruct the array and group by team

db.collection.aggregate([
  // match stage
  {
    $unwind: "$team"
  },
  {
    "$group": {
      "_id": "$team",
      "points": {
        "$sum": "$points"
      },
      goals_favor: {
        $sum: "$goals_favor"
      }
    }
  }
])

Working Mongo playground

READ ALSO
(Show more/Show less)For only one <td> in table in angular

(Show more/Show less)For only one <td> in table in angular

I have table and I want to use show more/ show less text posreport

46
What&#39;s wrong with my SQL Statement in python? [closed]

What's wrong with my SQL Statement in python? [closed]

Want to improve this question? Update the question so it's on-topic for Stack Overflow

82
Change ID and Class by click

Change ID and Class by click

i'm creating visual configurator for a car and wheels

45
Set up a SQL query with multiple AS

Set up a SQL query with multiple AS

I have a table called visits, which has columns source, statesource is string and state is boolean

43