$lastN
On this page
Definition
New in version 5.2.
$lastN
can be used as an aggregation accumulator or array operator. As
an aggregation accumulator, it an aggregation of the last n
elements within
a group. As an array operator, it returns the specified number of elements
from the end of an array.
Aggregation Accumulator
When $lasttN
is used as an aggregation accumulator, the elements returned
are meaningful only if they are in a specified sort order. If the group contains
fewer than n
elements, $lastN
returns all elements in the group.
Syntax
{ $lastN: { input: <expression>, n: <expression> } }
input
specifies the field(s) from the document to take the lastn
of. Input can be any expression.n
has to be a positive integral expression that is either a constant or depends on the_id
value for$group
. For details see group key example.
Behavior
Null and Missing Values
$lastN
does not filter out null values.$lastN
converts missing values to null.
Consider the following aggregation that returns the last five documents from a group:
db.aggregate( [ { $documents: [ { playerId: "PlayerA", gameId: "G1", score: 1 }, { playerId: "PlayerB", gameId: "G1", score: 2 }, { playerId: "PlayerC", gameId: "G1", score: 3 }, { playerId: "PlayerD", gameId: "G1"}, { playerId: "PlayerE", gameId: "G1", score: null } ] }, { $group: { _id: "$gameId", lastFiveScores: { $lastN: { input: "$score", n: 5 } } } } ] )
In this example:
$documents
creates the literal documents that contain player scores.$group
groups the documents bygameId
. This example has only onegameId
,G1
.PlayerD
has a missing score andPlayerE
has a nullscore
. These values are both considered as null.The
lastFiveScores
field is specified usinginput : "$score"
and returned as an array.Since there is no sort criteria the last 5
score
fields are returned.
[ { _id: "G1", lastFiveScores: [ 1, 2, 3, null, null ] } ]
Comparison of $lastN
and $bottomN
Both $lastN
and $bottomN
accumulators can accomplish similar
results.
In general:
If the documents coming into
$group
are already ordered, you should use$lastN
.If you're sorting and selecting the bottom
n
elements then you can use$bottomN
to accomplish both tasks with one accumulator.$lastN
can be used as an aggregation expression,$bottomN
cannot.
Restrictions
Window Function and Aggregation Expression Support
$lastN
is supported as an
aggregation expression.
$lastN
is supported as a
window operator
.
Examples
Consider a gamescores
collection with the following documents:
db.gamescores.insertMany([ { playerId: "PlayerA", gameId: "G1", score: 31 }, { playerId: "PlayerB", gameId: "G1", score: 33 }, { playerId: "PlayerC", gameId: "G1", score: 99 }, { playerId: "PlayerD", gameId: "G1", score: 1 }, { playerId: "PlayerA", gameId: "G2", score: 10 }, { playerId: "PlayerB", gameId: "G2", score: 14 }, { playerId: "PlayerC", gameId: "G2", score: 66 }, { playerId: "PlayerD", gameId: "G2", score: 80 } ])
Find the Last Three Player Scores for a Single Game
You can use the $lastN
accumulator to find the last three scores
in a single game.
db.gamescores.aggregate( [ { $match : { gameId : "G1" } }, { $group: { _id: "$gameId", lastThreeScores: { $lastN: { input: ["$playerId", "$score"], n:3 } } } } ] )
The example pipeline:
Uses
$match
to filter the results on a singlegameId
. In this case,G1
.Uses
$group
to group the results bygameId
. In this case,G1
.Specifies the fields that are output from
$lastN
withoutput : ["$playerId"," $score"]
.Uses
$lastN
to return the last three documents for theG1
game withn : 3
.
The operation returns the following results:
[ { _id: "G1", lastThreeScores: [ [ "PlayerB", 33 ], [ "PlayerC", 99 ], [ "PlayerD", 1 ] ] } ]
Finding the Last Three Player Scores Across Multiple Games
You can use the $lastN
accumulator to find the last n
input fields in each game.
db.gamescores.aggregate( [ { $group: { _id: "$gameId", playerId: { $lastN: { input: [ "$playerId","$score" ], n: 3 } } } } ] )
The example pipeline:
Uses
$group
to group the results bygameId
.Uses
$lastN
to return the last three documents for each game withn: 3
.Specifies the fields that are input for
$lastN
withinput : ["$playerId", "$score"]
.
The operation returns the following results:
[ { _id: 'G2', playerId: [ [ 'PlayerB', 14 ], [ 'PlayerC', 66 ], [ 'PlayerD', 80 ] ] }, { _id: 'G1', playerId: [ [ 'PlayerB', 33 ], [ 'PlayerC', 99 ], [ 'PlayerD', 1 ] ] } ]
Using $sort
With $lastN
Using a $sort
stage earlier in the pipeline can influence the
results of the $lastN
accumulator.
In this example:
{$sort : { score : -1 } }
sorts the highest scores to the back of each group.lastN
returns the three lowest scores from the back of each group.
db.gamescores.aggregate( [ { $sort : { score : -1 } }, { $group: { _id: "$gameId", playerId: { $lastN: { input: [ "$playerId","$score" ], n: 3 } } } } ] )
The operation returns the following results:
[ { _id: 'G2', playerId: [ [ 'PlayerC', 66 ], [ 'PlayerB', 14 ], [ 'PlayerA', 10 ] ] }, { _id: 'G1', playerId: [ [ 'PlayerB', 33 ], [ 'PlayerA', 31 ], [ 'PlayerD', 1 ] ] } ]
Computing n
Based on the Group Key for $group
You can also assign the value of n
dynamically. In this example,
the $cond
expression is used on the gameId
field.
db.gamescores.aggregate([ { $group: { _id: {"gameId": "$gameId"}, gamescores: { $lastN: { input: "$score", n: { $cond: { if: {$eq: ["$gameId","G2"] }, then: 1, else: 3 } } } } } } ] )
The example pipeline:
Uses
$group
to group the results bygameId
.Specifies the fields that input for
$lastN
withinput : "$score"
.If the
gameId
isG2
thenn
is 1, otherwisen
is 3.
The operation returns the following results:
[ { _id: { gameId: "G1" }, gamescores: [ 33, 99, 1 ] }, { _id: { gameId: "G2" }, gamescores: [ 80 ] } ]
Using $lastN
as an Aggregation Expression
You can also use $lastN
as an aggregation expression.
In this example:
$documents
creates the literal document that contains an array of values.$project
is used to return the output of$lastN
._id
is omited from the output with_id : 0
.$lastN
uses the input array of[10, 20, 30, 40]
.The last three elements of the array are returned for the input document.
db.aggregate( [ { $documents: [ { array: [10, 20, 30, 40] } ] }, { $project: { lastThreeElements:{ $lastN: { input: "$array", n: 3 } } } } ] )
The operation returns the following results:
[ { lastThreeElements: [ 20, 30, 40 ] } ]
Array Operator
Syntax
$lastN
has the following syntax:
{ $lastN: { n: <expression>, input: <expression> } }
Field | Description |
---|---|
n | An expression that resolves to a
positive integer. The integer specifies the number of array elements
that $lastN returns. |
input | An expression that resolves to the
array from which to return n elements. |
Behavior
$lastN
returns elements in the same order they appear in the input array.$lastN
does not filter outnull
values in the input array.You cannot specify a value of
n
less than1
.If the specified
n
is greater than or equal to the number of elements in theinput
array,$lastN
returns theinput
array.If
input
resolves to a non-array value, the aggregation operation errors.
Example
The collection games
has the following documents:
db.games.insertMany([ { "playerId" : 1, "score" : [ 1, 2, 3 ] }, { "playerId" : 2, "score" : [ 12, 90, 7, 89, 8 ] }, { "playerId" : 3, "score" : [ null ] }, { "playerId" : 4, "score" : [ ] }, { "playerId" : 5, "score" : [ 1293, null, 3489, 9 ]}, { "playerId" : 6, "score" : [ "12.1", 2, NumberLong("2090845886852"), 23 ]} ])
The following example uses the $lastN
operator to retrieve the
last three scores for each player. The scores are returned in the new field
lastScores
created by $addFields
.
db.games.aggregate([ { $addFields: { lastScores: { $lastN: { n: 3, input: "$score" } } } } ])
The operation returns the following results:
[{ "playerId": 1, "score": [ 1, 2, 3 ], "lastScores": [ 1, 2, 3 ] }, { "playerId": 2, "score": [ 12, 90, 7, 89, 8 ], "lastScores": [ 7, 89, 8 ] }, { "playerId": 3, "score": [ null ], "lastScores": [ null ] }, { "playerId": 4, "score": [ ], "lastScores": [ ] }, { "playerId": 5, "score": [ 1293, null, 3489, 9 ], "lastScores": [ null, 3489, 9 ] }, { "playerId": 6, "score": [ "12.1", 2, NumberLong("2090845886852"), 23 ], "lastScores": [ 2, NumberLong("2090845886852"), 23 ] }]