Getting the max for the first element in an array?

Question

Say I have documents like the ones below:

{
   email: "[email protected]",
   values: [2,9]
},
{
   email: "[email protected]",
   values: [1,3]
},
{
   email: "[email protected]",
   values: [4,5]
},

And I would like to get the maximum value for the first element in the values array for each email (so 2 for [email protected]. Is it possible to apply $max only to the first element of the array? I tried the methods below:

db.test.aggregate({$group:{_id: '$email', max: {$max: "$values[0]"}}})

and

db.test.aggregate({$group:{_id: '$email', max: {$max: "$values.0"}}})

but neither seem to work (they return either null or an empty array for max). Can this be done?

Answer 1

Close try, but unfortunately the standard form of projection available to methods like .find() are not available to the aggregation framework. You have to get a little more involved to get what you want.

db.test.aggregate([
    // Unwind the arrays
    { "$unwind": "$values" },

    // Get the first value out of each document. Yes, order is respected.
    { "$group": {
        "_id": "$_id",
        "email": { "$first": "$email" },
        "firstValue": { "$first": "$values" }
    }},

    // Then get "max" per grouping key
    { "$group": {
        "_id": "$email",
        "value": { "$max": "$firstValue" }
    }}
])

This works because $first pulls the "first" match on the "grouping boundary" and the order of the array is respected when unwinding the array content.

So the "first" $group stage gets the "first" array element in the document. And the "second" $group stage performs the $max over all of those values once they have been extracted.

P.S. Don't ask how to get the "nth" value as that is another question entirely, and much more involved. If you follow it through, there is a logical progression to do that though. It's not an optimal solution, but it can be done.

Believe me, in that we have been hanging out for "slice" projections and "limits" on $push operations for some time now. It may happen some day.