Exclude 0 values from mongodb $avg but keeping other fields

Question

I run some aggregation queries on MongoDB 3.2. I would like to group documents by a field with an average on another numeric field. I need the average to ignore the 0 values. The problem is I can't entirely filter the document, cause there is another field I need for a count.

Let's illustrate :

This is the structure of my documents:

{"stringToGroupByOn":"foo", "valueToAvg":42, "valueToSum":21}
{"stringToGroupByOn":"foo", "valueToAvg":0, "valueToSum":13}

I can't just filter like this:

db.foobar.aggregate([
    { 
        $match : { valueToAvg : { $gt : 0 } } 
    },
    { 
        $group : {
            _id : '$stringToGroupByOn',
            avg : { $avg :  '$valueToAvg' }, 
            count : { $sum : '$valueToSum' }
        }
    }
])

Because I lose the value 13 for the count.

Do you think there is a way to do it in only one query ?

Answer 1

You can use $cond in projection to set null instead of 0, as null is not considered when using average.

db.avg.aggregate([
   {$project:{
       _id:1,
       valueToSum:1,
       stringToGroupByOn:1,
       valueToAvg:{$cond: 
             { if: { $eq: [ "$valueToAvg", 0 ] }, 
                   then: null, 
                   else: "$valueToAvg" }}           
       }},
    { 
        $group : {
            _id : '$stringToGroupByOn',
            avg : { $avg :  '$valueToAvg' }, 
            count : { $sum : '$valueToSum' }
        }
    }

output:

{
    "_id" : "foo",
    "avg" : 42.0,
    "count" : 34.0
}