Conditional $first in mongodb aggregation

Question

I am trying to figure out if I can somehow combine $first and $ifnull or $cond in a mongodb aggregation method. Assuming the following documents:

{ "_id" : 1, "item" : "box" , "code" : null }
{ "_id" : 2, "item" : "box" , "code" : "abcde" }
{ "_id" : 3, "item" : "box" , "code" : "abcde" }
{ "_id" : 4, "item" : "box" , "code" : null }

I then run the following aggregation method to group documents together:

db.items.aggregate([{
    $group : {
        _id : '$item',
        code :  { $first : '$code' }
    } 
}])

My aggregation result is:

{ "result" : [ { "_id" : "box", "code" : null } ], "ok" : 1 }

I would like to know if there is a way to combine the $first operand with $ifnull or $cond in order to get the code field which is not null. So my result would look like this:

{ "result" : [ { "_id" : "box", "code" : 'abcde' } ], "ok" : 1 }

Cheers,

Answer 1

Not in the way you want this to happen there isn't. You seem to be looking for a "conditional" way to evaluate $first and this just does not happen. All $ifNull can offer you is that where the field does not actually "exist" or otherwise evaluates to null then an "alternate" value is returned.

But you don't want this, and all it seems you basically want is to simply "filter" the possible results via a $match statement first:

db.items.aggregate([
    { "$match": { "code": { "$ne": null } } },
    { "$group": { 
        "_id": "$item",
        "code": { "$first": "$code" }
    }}
])

But really, in your case and also unless you specifically need a "sort order" to come to the result, with a singular field you are better off just using $max as an operator, even if a $match stage does actually "still" optimize this by excluding any documents that actually "should not be" part of the desired result:

db.items.aggregate([
    { "$group": { 
        "_id": "$item",
        "code": { "$max": "$code" }
    }}
])

So $match and $max in the $group is probably what you really want in this case. The $first operator usually only makes sense when you want multiple fields from the document as a result and then only usually after a $sort operation unless your documents expect a "natural" sort order as they appear on disk ( and we mean that exactly, things can move around ).

So consider what you actually want to do and implement accordingly.