I have 4 collections of players stats: Laliga2017,Laliga2018,Laliga2019 and Laliga2020

{
    '_id': ObjectId('61eda25219e7e44801799d71'),
    'name': 'Sergio Ramos',
    'age': 34,
    'nationality': 'Spain',
    'height': 184,
    'weight': 82,
    'team': 'Real Madrid',
    'position': 'DF',
    'general_stats': {
        'games': 15,
        'time': 1280,
        'red_cards': 0,
        'yellow_cards': 3,
    },
    'offensive_stats': {
        'goals': 2,
        'xG': 3.95,
        'assists': 0,
        'xA': 0.4,
        'shots': 17,
        'key_passes': 4,
        'npg': 0,
        'npxG': 2.46,
        'xGChain': 9.06,
        'xGBuildup': 7.22,
    },
    'defensive_stats': {
        'Tkl': 20,
        'TklW': 12,
        'Past': 8,
        'Press': 97,
        'Succ': 39,
        'Blocks': 18,
        'Int': 16,
    },
    'passing_stats': {
        'Cmp': 1023,
        'Cmp%': 91.8,
        '1/3': 106,
        'PPA': 2,
        'CrsPA': 1,
        'Prog': 61,
    },
}

Is there a way to get the sum of all the goal_contribution(goals assists) for all 4 years (for each year and then sum of all the years stats)?

I reached here:

db.LaLiga_2020.aggregate(
    [

        { $match: { name: 'Lionel Messi' } },
        {
            $lookup: {
                from: 'LaLiga_2019',
                localField: 'name',
                foreignField: 'name',
                as: 'stats2019',
            },
        },
        {
            $lookup: {
                from: 'LaLiga_2018',
                localField: 'name',
                foreignField: 'name',
                as: 'stats2018',
            },
        },
        {
            $lookup: {
                from: 'LaLiga_2017',
                localField: 'name',
                foreignField: 'name',
                as: 'stats2017',
            },
        },
        {
            $project: {
                _id: 0,
                name: 1,
                team: 1,
                position: 1,
                goal_cotribution_2020: { $add: ['$offensive_stats.goals', '$offensive_stats.assists'] },
                goal_cotribution_2019: { $sum: ['$stats2019.offensive_stats.goals', '$stats2019.offensive_stats.assists'] },
                goal_cotribution_2018: { $sum: ['$stats2018.offensive_stats.goals', '$stats2018.offensive_stats.assists'] },
                goal_cotribution_2017: { $sum: ['$stats2017.offensive_stats.goals', '$stats2017.offensive_stats.assists'] },
            },
        },
    ],
).pretty();

But it returns:

{
    'name': 'Lionel Messi',
    'team': 'Barcelona',
    'position': 'FW',
    'goal_cotribution_2020': 39,
    'goal_cotribution_2019': 0,
    'goal_cotribution_2018': 0,
    'goal_cotribution_2017': 0,
};

CodePudding user response：

The issue is from your $project stage, specifically how you $sum:

goal_cotribution_2018: {
  $sum: [
    "$stats2018.offensive_stats.goals",
    "$stats2018.offensive_stats.assists"
  ]
}

The problem is that stats2018 is an array after the $lookup stage. so "$stats2018.offensive_stats.goals" resolves into an array of numbers and not a number, imagine what you're doing is this:

goal_cotribution_2018: {
  $sum: [
    [12],
    [5]
  ]
}

So while there is no error thrown this is undefined behavior and $sum just returns 0.

You can solve this in several different ways. you could $unwind or use $arrayElemAt, But I think the "cleanest" way is just to add an additional nested $sum, like so:

goal_cotribution_2018: {
  $sum: [
   {
     $sum: "$stats2019.offensive_stats.goals"
   },
   {
     $sum: "$stats2019.offensive_stats.assists"
   },
  ]
},

CodePudding user response：

Simplest way is adding all the fields at the end should work

{
            $project: {
                _id: 0,
                name: 1,
                team: 1,
                position: 1,
                goal_cotribution_2020: { $add: ['$offensive_stats.goals', '$offensive_stats.assists'] },
                goal_cotribution_2019: { $add: ['$stats2019.offensive_stats.goals', '$stats2019.offensive_stats.assists'] },
                goal_cotribution_2018: { $add: ['$stats2018.offensive_stats.goals', '$stats2018.offensive_stats.assists'] },
                goal_cotribution_2017: { $add: ['$stats2017.offensive_stats.goals', '$stats2017.offensive_stats.assists'] },
                total_goal_cotribution: { $add: ['$offensive_stats.goals', '$offensive_stats.assists','$stats2019.offensive_stats.goals', '$stats2019.offensive_stats.assists', '$stats2018.offensive_stats.goals', '$stats2018.offensive_stats.assists','$stats2017.offensive_stats.goals', '$stats2017.offensive_stats.assists'] }
            },
        }