$sum with multiple conditions

Question

I need to convert a current $group with $sum to add another condition:

Actually sum a field if a $eq return true.

Now I want to do sum a field if $eq return true and $exists return true.

My current group its this:

"techsWorkQty": {
                "$sum": {
                    "$cond": [
                        { "$eq": [
                            "$works.TechnicianId", id
                        ]},
                        1,
                        0
                    ]
                }
            }

Where can I add the following subquery?

{
 $works.percent:{$exists:true}
}

Answer 1

The $exists operator is a "query" operator only and can only be used in a $match pipeline for aggregation. In order to test for a field presence as a "logical" operator you want to use $ifNull. The $and operator is used to combine logical conditions and is also a logical operator as opposed to the query form of the same name. Logical operators can be used in $project and $group stages:

"techsWorkQty": {
    "$sum": {
        "$cond": [
            { "$and": [
                { "$ifNull": [ "$works.percent", false ] },
                { "$eq": [ "$works.TechnicianId", id ] }
            ]},
            1,
            0
        ]
    }
}

$ifNull returns either the field value where it is present or the alternate value specified where the field does not exist. In this case a "value" for a present field would evaluate to true in boolean context. Where there is not a field then the false argument is returned. The $and in boolean form requires both logical expressions to evaluate to true, otherwise in itself it returns false which is handled by the wrapping $cond to return the appropriate value.

Answer 2

Project.aggregate([
{
            "$unwind": "$works"
        },
$group: {
_id:null,
techsWorkQty: {
                $sum: {
                    $cond: {
                        if: {
                            $eq: ['$works.percent', id]
                        },
                        then: 1,
                        else: 0
                    }
                }
            }
      }
])