Getting max for the first element in an array?
Let's say I have docs like the following:
{
email: "test@test.com",
values: [2,9]
},
{
email: "test@test.com",
values: [1,3]
},
{
email: "another@email.com",
values: [4,5]
},
And I would like to get the maximum value for the first item in the array values
for each letter (so 2
for test@test.com
). Can I $max
only apply to the first element of an array? I've tried the following methods:
db.test.aggregate({$group:{_id: '$email', max: {$max: "$values[0]"}}})
and
db.test.aggregate({$group:{_id: '$email', max: {$max: "$values.0"}}})
but none of them work (they return either null
an empty array for max
). It can be done?
source to share
Quit trying, but unfortunately the standard design form available for methods like .find()
is not available for the aggregation framework. You need to do a little work 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 it pulls the "first" match at the "grouping boundary" and the array order is respected when unwinding the array contents. $first
Thus, the first stage gets the first array element in the document. And the second stage executes on all these values after they have been extracted. $group
$group
$max
PS Don't ask how to get the "n-th" value, as that is another question and much more. If you follow this, there is logical progress to do so. This is not an optimal solution, but it can be done.
Trust me, we have been hanging out behind "shear" projections and "constraints" on for some time. It might happen someday. $push
source to share