Details
-
Improvement
-
Resolution: Done
-
Major - P3
-
None
-
Fully Compatible
-
QE 2021-10-18, QE 2021-11-29, QE 2021-12-13
-
(copied to CRM)
Description
Summary
- Accumulators that return an array of top/bottom n elements for each group according to the specified order
{$topN: {n: <n expression>,sortBy: <sort spec>,output: <expression>}}{$bottomN: {n: <n expression>,sortBy: <sort spec>,output: <expression>}}To return one top/bottom element:
{$top: {sortBy: <sort spec>,output: <expression>}}{$bottom: {sortBy: <sort spec>,output: <expression>}}
- Accumulators that return first/last n elements for each group according to the current order of the documents:
{$firstN: {n: <n expression>,input: <expression>}}{$lastN: {n: <n expression>,input: <expression>}}
- Accumulators that return n highest/lowest values for each group
{$maxN: {n: <n expression>,input: <expression>}}{$minN: {n: <n expression>,input: <expression>}}
- $firstN/$lastN/$minN/$maxN are also available as aggregation expressions for array fields.
Examples
db.scores.insert([
|
{user: "user1", game:"game_A", score: 2345},
|
{user: "user2", game:"game_A", score: 100},
|
{user: "user3", game:"game_A", score: 555},
|
{user: "user4", game:"game_A", score: 53234},
|
{user: "user1", game:"game_B", score: 65438},
|
{user: "user2", game:"game_B", score: 865},
|
{user: "user3", game:"game_B", score: 400},
|
{user: "user4", game:"game_B", score: 9865}
|
]);
|
|
|
db.scores.aggregate([
|
{
|
$group:
|
{
|
_id: "$game",
|
leaderboard:
|
{
|
$topN:
|
{
|
sortBy: {score: -1},
|
n: 3,
|
output: {userName: '$user', score: '$score'}
|
}
|
}
|
}
|
}
|
])
|
Output
[{
|
user: "game_A",
|
leaderboard: [
|
{userName: "user4", score: 53234},
|
{userName: "user1", score: 2345},
|
{userName: "user3", score: 555},
|
]
|
},
|
{
|
user: "game_B",
|
leaderboard: [
|
{user: "user1", score: 65438},
|
{user: "user4", score: 9865},
|
{user: "user2", score: 865},
|
]
|
}]
|
db.scores.aggregate( [
|
{
|
$group:
|
{
|
_id: "$game",
|
threeHighestScores:
|
{
|
$maxN:
|
{
|
input: "$score",
|
n: 3
|
}
|
}
|
}
|
}
|
] )
|
Output:
[{
|
_id: "game_A",
|
threeHighestScores: [ 53234, 2345, 555]
|
},
|
{
|
_id: "game_B",
|
leaderboard: [
|
threeHighestScores: [ 65438, 9865, 865]
|
}]
|
Versions
This feature will be available starting in version 5.2.0 once the upgrade (including upgrading the FCV) is fully complete.
Original Description
Title: Extend $push or $max to allow collecting "top" N values per _id key in $group phase
Syntax
{$pushFirstN: [<expression>, N]}
|
{$pushLastN: [<expression>, N]}
|
{$pushMinN: [<expression>, N]}
|
{$pushMaxN: [<expression>, N]}
|
Examples
> db.coll.insert([
|
{_id: "user1", game:"A", score: 2345},
|
{_id: "user3", game:"A", score: 100},
|
{_id: "user4", game:"A", score: 555},
|
{_id: "user7", game:"A", score: 53234}
|
]);
|
> db.coll.aggregate([
|
{$sort: {score: -1}},
|
{$group: {
|
_id: "$game",
|
leaderBoardUsers: {
|
$pushFirstN: ["$_id", 10]
|
}
|
}
|
])
|
{_id: "A", leaderBoardUsers: ["user7", "user1", "user4", "user3"]}
|
|
|
|
|
// Example 2
|
> db.coll.insert([
|
{_id: "user1", game:"A", score: 2345}
|
{_id: "user3", game:"A", score: 100}
|
{_id: "user4", game:"A", score: 555}
|
{_id: "user7", game:"A", score: 53234}
|
]);
|
> db.coll.aggregate([
|
{$sort: {score: -1}},
|
{$group: {
|
_id: "$game",
|
leaderBoard: {$pushFirstN: [{_id: "$_id", score: "$score"}, 10]}
|
}
|
])
|
{
|
_id: "A",
|
leaderBoard: [
|
{_id: "user7", score: 53234},
|
{_id: "user1", score: 2345},
|
{_id: "user4", score: 555},
|
{_id: "user3", score: 100}
|
]
|
}
|
Notes
- This would be the first accumulator to take more than a single argument.
Errors
- If the second argument is not a nonnegative integer. If the argument is zero, the result will be an empty array.
Previous Description:
Frequently asked by users:
Analogous to {$group:{_id:"$key", maxval:{$max:"$val"}}} if user needs to gather top N values per key (most recent, highest N, etc) to have ability to do equivalent of {$max:"$val",$limit:5} or $push:{$sort:...,$limit:N} type of idea.
Attachments
Issue Links
- is duplicated by
-
SERVER-10205 Group and select the top K elements in each group
-
- Closed
-
-
SERVER-16989 Add $group accumulation operators $first-n and $last-n
-
- Closed
-
-
SERVER-43928 Allow group aggregations to limit collected records
-
- Closed
-
-
SERVER-7618 New aggregation expression: generator for serial numbers
-
- Closed
-