sum number of occurrences on two fields in mongodb

Question

I have a collection of documents called games who looks like this

{ winner: 'oakis', loser: 'test' },
{ winner: 'test', loser: 'oakis' }

Now I would like MongoDB to output this:

{ _id: 'oakis', wins: 1, losses: 1 },
{ _id: 'test', wins: 1, losses: 1 }

I have been looking at aggregation, mapReduce and distinct with no success. I'm hoping you can help me!

EDIT:

I have a field called "tied" which is a boolean as well. How can I implement tied games as well in the results? (There is also a field for each player in the game, player1 & player2) Result should be: { _id: 'name', wins: 3, losses: 1, tied: 1 }

EDIT2:

Solved it like this!

db.games.aggregate([
    { 
        $project: { 
            scores: [
                { name: '$winner', wins: { $literal: 1 }, losses: { $literal: 0 }, tied: { $literal: 0 } }, 
                { name: '$loser', wins: { $literal: 0 }, losses: { $literal: 1 }, tied: { $literal: 0 } },
        { name: '$player1', wins: { $literal: 0 }, losses: { $literal: 0 }, tied: { $cond: [ "$tied", 1, 0 ] } },
        { name: '$player2', wins: { $literal: 0 }, losses: { $literal: 0 }, tied: { $cond: [ "$tied", 1, 0 ] } }
            ] 
        } 
    }, 
    { 
        $unwind: '$scores' 
    }, 
    { 
        $group: {
             _id: "$scores.name", 
            wins: { $sum: "$scores.wins" }, 
            losses: { $sum: "$scores.losses" },
    tied: { $sum: "$scores.tied" }
        } 
    }
])

DAXaholic · Accepted Answer · 2016-06-04T11:18:06.313

With the aggregation framework you could use a pipeline like the following. Essentially it first projects the data into a 2 element array of the the form [{name: $winner, wins: 1, losses: 0}, {name: $loser, wins: 0, losses: 1}], then unwinds the array to produce single element arrays which are then grouped by name and the wins and loses are summed up.

games.aggregate(
    [
        { 
            $project: { 
                scores: [
                    { name: '$winner', wins: { $literal: 1 }, losses: { $literal: 0 } }, 
                    { name: '$loser', wins: { $literal: 0 }, losses: { $literal: 1 } }
                ] 
            } 
        }, 
        { 
            $unwind: '$scores' 
        }, 
        { 
            $group: {
                 _id: "$scores.name", 
                wins: { $sum: "$scores.wins" }, 
                losses: { $sum: "$scores.losses" } 
            } 
        }
    ]
)

I have a field called "tied" which is a boolean as well. How can I implement tied games as well in the results? (There is also a field for each player in the game, player1 & player2) Result should be: `{ _id: 'name', wins: 3, losses, 1, tied: 1 }` — Anders Ekman, Jun 05 '16 at 06:14

sum number of occurrences on two fields in mongodb

1 Answers1