some problems with mongoDB - how to get last row value exist on group condition

Question

I have some problem with mongo.

I need to get last name of a row if exist. mongo document { _id: Object... campaign: 1 total: 1 ...: . .. campaignName: "name of campaign"
}, { _id: Object... campaign: 2 total:3 ...: . .. campaignName: "name of campaign 2"
}, { _id: Object... campaign: 1 total: 1 ...: . .. campaignName: "z name of campaign"
}, { _id: Object... campaign: 1 total: 6 ...: . .. } how he see the "campaignName" is missing on tree position of the collection

If i do this:

db.dataweek.aggregate([{
    $project: {
        "campaignName": 1
    }
}, {
    $group: {
        _id: { "$_id", "$campaign" },
        campaignName: {
            $last: "$campaignName"
        },
        total: {
            $sum: "$total"
        }
    }
}])

I get the result ok if all the last rows its have a field campaignName, but if not I get a null name for this field.

My question is how to get the last campaignName value which exists ( group campaign ) .

and the result should be like:

campaign: 1 total: 8 .. campaignName: "z name of campaign"

campaign: 2 total: 3 .. campaignName: "name of campaign 2"

....

you can see the name of campaign 1 has changed

PS. $first is not valid, i need the last campaignName exists but diferrent to ''

Answer 1

In a nutshell you need to $sort the documents first before grouping so that the expected result is last. Consider the following sample:

{ "a" : 1, "title" : "here" }
{ "a" : 1 }
{ "a" : 1, "title" : "there" }
{ "a" : 2 }
{ "a" : 2, "title" : "here" }
{ "a" : 2, "title" : "there" }
{ "a" : 1 }
{ "a" : 2 }

So to find the last valid "title" when grouping by "a" and obtaining the count, first sort by the grouping key and the "title". Entries that do not contain the title will be listed first:

db.collection.aggregate([
    { "$sort": { "a": 1, "title": 1 } },
    { "$group": {
        "_id": "$a",
        "title": { "$last": "$title" },
        "total": { "$sum": 1 }
    }}
])

Of course the results are sorted on that field so "t" would come after "h" in this example. So if you had another result to "sort" on such as a "date" which was important then you would include that in the sort order first:

{ "$sort": { "a": 1, "date": 1, "title": 1 } }

But there really would be no other way to ensure this with $last as you cannot say

"Last if the value exists"

The $exists clause for the query portion would remove entries where this was not present, so that would change the "count" on results. There is a $ifNull operator which evaluates whether the field is present and alternately returns another value, but that really is no use to you without a "sort".

Also note that for a single field where you are not looking for multiple fields or the "last" entry by some other sorted condition, the $max operator does the same thing without having to apply a sort.