CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
YulePale
YulePale

Reputation: 7686

How to $sum or $subtract in mongodb aggregation depending on a certain condition

Assume I have a model that looks like this

userId: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true,
},
points: {
    type:Number,
    required: true,
},
wonOrLost:{
    type: String,
    required: true,
    enum: ['won', 'lost', 'noResults'],
    default: 'noResults',
},

Then I have aggregation code that looks like this:

let _points = await Points.aggregate([
    { $match: {
        userId: user._id ,
        wonOrLost: { $ne: "noResults" }
    }},
    { $group: {
        _id: "$userId",
        // here(totalPoints) I want to add points if wonOrLost == 'won' and subtract if wonOrLost == 'lost'
        totalPoints: { $sum: "$points" }, 
    }}    
])

Upvotes: 1

Views: 1379

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22276

Here is how I would do it using $cond, the strategy would be to multiply the points by -1 if the match was lost before summing.

let _points = await Points.aggregate([
    {
        $match: {
            userId: user._id,
            wonOrLost: {$ne: 'noResults'}
        }
    },
    {
        $group: {
            _id: '$userId',
            totalPoints: {$sum: {$cond: [{$eq: ["$wonOrLost", "won"]}, '$points', {$multiply: ['$points', -1]}]}},
        }
    }
]);

Upvotes: 2

Related Questions