Conditional $sum in MongoDB

aggregation-framework, mongodb, mongodb-query

Solution

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

Problem

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

Original source