Conditional $sum in MongoDB

mongodb find sum of field
mongodb conditional query
cond mongodb
mongodb addfields condition
mongodb group condition
mongodb project condition
mongodb conditional update
mongodb aggregate sum string

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

$sum (aggregation) — MongoDB Manual, If all operands are non-numeric, $sum returns 0 . Example, Field Values, Results. { $sum : <� In MongoDB 3.2 and earlier, $sum is available in the $group stage only. When used in the $group stage, $sum has the following syntax and returns the collective sum of all the numeric values that result from applying a specified expression to each document in a group of documents that share the same group by key:

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 }

$cond (aggregation) — MongoDB Manual, $strLenCP (aggregation) � $substr (aggregation) � $substrBytes (aggregation) � $substrCP (aggregation) � $subtract (aggregation) � $sum (aggregation)� Here is the query to aggregate sum in MongoDB to get the total sum. You can see in the results that after running the query we got the total number of available records which is 4. Let’s go with the other example where we sum up the amount:

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] }

Conditional sum aggregation pipeline, Is it possibile to create a pipeline for something like this? I wanted the value in the aggregated collection to be calculated according to the� Conditional Aggregation on Arrays of Objects in MongoDB get a sum of alpha and a count of bravo We don't care MongoDB's Aggregation Framework is great working

Mongodb Important Queries. 1. How to group query with multiple , Conditional $sum in MongoDB. db.Sentiments.aggregate( { $project: { _id: 0, Company: 1, PosSentiment: {$cond: [{$gt: ['$Sentiment', 0]},� Perform SUM and SUBTRACTION on the basis of a condition in a single MySQL query? MongoDB query with an 'or' condition? SUM a column based on a condition in MySQL; Write down the MySQL query which shows equality condition? Write down the MySQL query which shows inequality condition? Fetch multiple documents in MongoDB query with OR condition?

How to Calculate a Sum in MongoDB, What is the use of Sum in MongoDB and how to use it? The $sum operator is used to sum up the values of fields in documents. Today, I'm going� Upgrade MongoDB Community to MongoDB Enterprise. Upgrade to MongoDB Enterprise (Standalone) Upgrade to MongoDB Enterprise (Replica Set) Upgrade to MongoDB Enterprise (Sharded Cluster) Verify Integrity of MongoDB Packages; The mongo Shell. Configure the mongo Shell; Access the mongo Shell Help; Write Scripts for the mongo Shell; Data Types in

Conditional Aggregation on Arrays of Objects in MongoDB, Checking out the documentation for the aggregation operator $cond, the example uses $sum to aggregate conditionally on a field value. To understand the MongoDB group by multiple fields first, let’s have a look at a list of all operators that can be used in $ group: $ sum – Returns the sum of all numeric fields. $ avg – Calculates the average between numeric fields. $ min – Returns the minimum value from the numeric field

Comments
  • You might be able to use a $cond in the sum: docs.mongodb.org/manual/reference/aggregation/… but it sounds like a butchery of speed and scalable querying, I can imagine this query was slow in SQL and will be slow here on a medium result set
  • @Sammaye I am trying to replace '?' with {$cond: { Sentiment: { $gte: 0} } } . But that looks like a wrong syntax.. I'm not getting any output.
  • $cond works like an if statement exactly like a case, so the first expression would be: Sentiment >0 and then Sentiment and then 0 for the first $cond in the first $sum