Is there any way to use $filter and $sum in the same $project?

Question

Let's say I have a document in my aggregate pipeline that looks like this:

{
  scores: [
    {type: 'quiz', score: 75},
    {type: 'quiz', score: 62},
    {type: 'final', score: 34},
  ]
}

I'm using $project to transform it, and I'd like to get the sum of the the quiz scores, is there a way I could somehow chain my $filter and $sum.

I know that I could potentially use two $projects, but the way I currently have my pipeline set up would force me to continue reproject a ton of keys in my second project, which I'd like to avoid.

Answer 1

You would need help from another operator that maps the scores embedded documents into an array of values that you can $sum. You can do that using the $map operator as in the following:

db.test.aggregate([ 
    { 
        "$project": { 
            "scores": 1,
            "quiz_total": { 
                "$sum": {
                    "$map": { 
                        "input": "$scores", 
                        "as": "item", 
                        "in": { 
                            "$cond": [ 
                                { "$eq": [ "$$item.type", "quiz" ] }, 
                                "$$item.score", 
                                0 
                            ]
                        }
                    }
                }                
            }    
        }   
    }
])

Sample Output

{
    "_id" : ObjectId("582ac9619602e37d2794acd3"),
    "scores" : [ 
        {
            "type" : "quiz",
            "score" : 75
        }, 
        {
            "type" : "quiz",
            "score" : 62
        }, 
        {
            "type" : "final",
            "score" : 34
        }
    ],
    "quiz_total" : 137
}

Answer 2

YES!

db.collection.aggregate([ 
    { "$project": { 
        "totalQuiz": { 
            "$sum": { 
                "$filter": {
                    "input": "$score", 
                    "as": "s",
                    "cond": { "$eq": [ "$$s.type", "quiz" ] }
                }
             }
        }
    }
])