CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
afreeland
afreeland

Reputation: 3979

Subtract group aggregate values in MongoDB

I have a collection that stores a users voting value voteTypeID = 1 for up vote and voteTypeID = 2 for a down vote and a loadoutID that ties back to another collection.

I want to group by loadoutID and take all up-votes and subtract all down-votes, to get the difference. So if a loadout has 20 up-votes and 2 down-votes, I would like to get back 18 as well as the loadoutID.

I admit I am fairly new to the aggregate function and have only used it for basic counts before and it just hasnt clicked yet for me, any help greatly appreciated. The more you could explain the aggregate the better =)

Here is my most recent failed attempt

LoadoutVotes.aggregate([
    {
        $group: {
            _id: '$loadoutID',
            up: { $sum: { voteTypeID: 1}},
            down: { $sum: { voteTypeID: 2 }}
        }
    },
    {
        $project: {
            _id: '$loadoutID',
            count: {$subtract: ['$up', '$down']}
        }
    }
])

JSON data

{
  loadoutID: 'ObjectID-String',
  voteTypeID: 1
}

Upvotes: 3

Views: 5633

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

The $sum operator in the aggregation framework takes an argument that is a numeric value which is applied to the grouping. Typically you want this to be either the value of a field in the document or a value like 1 when you want to count the occurrences.

What you are trying to do is "conditionally" evaluate a field value, and for this there is the $cond operator. This would determine which value to apply depending on your voteTypeID field. So you could write like this:

LoaodoutVotes.aggregate([
    { "$group": {
       "_id": "$loadoutID",
       "up": { 
           "$sum": { 
               "$cond": [
                   { "$eq": [ "$voteTypeID", 1 ] },
                   1,
                   0
               ]
           }
       },
       "down": { 
           "$sum": { 
               "$cond": [
                   { "$eq": [ "$voteTypeID", 2 ] },
                   1,
                   0
               ]
           }
       },
    }},
    { "$project": {
       "loadoutID": "$_id",
       "count": { "$subtract": [ "$up", "$down" ] }
    }}
])

Or even more efficiently like this as you can "sign" the values in-line:

LoaodoutVotes.aggregate([
    { "$group": {
       "_id": "$loadoutID",
       "count": {
           "$sum": {
               "$cond": [
                   { "$eq": [ "$voteTypeID", 1 ] },
                   1,
                   { "$cond": [
                      { "$eq": [ "$voteTypeID", 2 ] },
                      -1,
                       0
                   ]}
               ]
           }
       }
    }}
])

So it is much more efficient as $group is now just a single pass in the pipeline. Since $cond is a ternary operator ( if-then-else ) then the conditions can be stacked in-line as well. So either positive or negative or nothing.

Upvotes: 7

Related Questions