Conditional $sum in MongoDB

Question

My collection in mongodb is similar to the following table in SQL:

Sentiments(Company,Sentiment)

Now, I need to execute a query like this:

SELECT   Company,    SUM(CASE WHEN Sentiment >0 THEN Sentiment ELSE 0 END) AS SumPosSenti,    SUM(CASE WHEN Sentiment <0 THEN Sentiment ELSE 0 END) AS SumNegSenti FROM Sentiments GROUP BY Company 

What should I do to write this query in Mongo? I am stuck at the following query:

db.Sentiments.aggregate( { $project: {_id:0, Company:1, Sentiment: 1} }, { $group: {_id: "$Company", SumPosSenti: {$sum: ? }, SumNegSenti: {$sum: ? } } } ); 

Answer 1

As Sammaye suggested, you need to use the $cond aggregation projection operator to do this:

db.Sentiments.aggregate(     { $project: {         _id: 0,         Company: 1,         PosSentiment: {$cond: [{$gt: ['$Sentiment', 0]}, '$Sentiment', 0]},         NegSentiment: {$cond: [{$lt: ['$Sentiment', 0]}, '$Sentiment', 0]}     }},     { $group: {         _id: "$Company",         SumPosSentiment: {$sum: '$PosSentiment'},         SumNegSentiment: {$sum: '$NegSentiment'}     }}); 

Answer 2

Starting from version 3.4, we can use the $switch operator which allows logical condition processing in the $group stage. Of course we still need to use the $sum accumulator to return the sum.

db.Sentiments.aggregate(     [         { "$group": {              "_id": "$Company",               "SumPosSenti": {                  "$sum": {                      "$switch": {                          "branches": [                              {                                  "case": { "$gt": [ "$Sentiment", 0 ] },                                  "then": "$Sentiment"                             }                         ],                          "default": 0                      }                 }             },              "SumNegSenti": {                 "$sum": {                      "$switch": {                          "branches": [                              {                                  "case": { "$lt": [ "$Sentiment", 0 ] },                                  "then": "$Sentiment"                             }                         ],                          "default": 0                      }                  }             }         }}     ] ) 

---

If you have not yet migrated your mongod to 3.4 or newer, then note that the $project stage in this answer is redundant because the $cond operator returns a numeric value which means that you can $group your documents and apply $sum to the $cond expression.

This will improve the performance in your application especially for large collection.

db.Sentiments.aggregate(     [         { '$group': {             '_id': '$Company',             'PosSentiment': {                  '$sum': {                     '$cond': [                         { '$gt': ['$Sentiment', 0]},                          '$Sentiment',                          0                     ]                 }             },             'NegSentiment': {                  '$sum': {                     '$cond': [                         { '$lt': ['$Sentiment', 0]},                          '$Sentiment',                          0                     ]                 }             }         }}     ] ) 

---

Consider a collection Sentiments with the following documents:

{ "Company": "a", "Sentiment" : 2 } { "Company": "a", "Sentiment" : 3 } { "Company": "a", "Sentiment" : -1 } { "Company": "a", "Sentiment" : -5 } 

The aggregation query produces:

{ "_id" : "a", "SumPosSenti" : 5, "SumNegSenti" : -6 }