Styjun

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: ? 
);

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'
 );

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 

Explaining the snippets above, that uses the array syntax:

PosSentiment: $cond: [$gt: ['$Sentiment', 0], '$Sentiment', 0]

is equal to:

PosSentiment: $cond: if: $gt: ['$Sentiment', 0], then: '$Sentiment', else: 0 

The array syntax summarizes the long syntax to just $cond: [if, then, else]