$bottomN聚合運算符返回分組中指定順序的最后n個元素，如果分組中的元素數量小于n，則返回分組的全部元素。從MongoDB5.2開始支持。

語法

{$bottomN:{n: <expression>,sortBy: { <field1>: <sort order>, <field2>: <sort order> ... },output: <expression>}
}

• n用于限制每組結果的數量，必須是正整數表達式，要么是常數，要么取決于$group的_id 值
• sortBy制定返回結果的順序，語法類似于$sort
• output指定分組元素輸出的內容，可以是任何合法的表達式。

用法

• $bottom不支持作為聚合表達式。
• $bottom只支持作為window 操作符。
• 聚合管道調用$bottom受100M的限制，如果單組超過這一限制將報錯。

關于null和缺失值的處理

• $bottom不會過濾掉空值
• $bottom會將缺失值轉換為null

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",playerId:{$bottomN:{output: [ "$playerId", "$score" ],sortBy: { "score": -1 },n: 3}}}}
] )

在這個例子中：

• 使用$documents階段創建了一些字面量（常量）文檔，包含了選手的得分
• $group階段根據gameId對文檔進行了分組，顯然文檔中的gameId都是G1
• PlayerD的得分缺失，PlayerE的得分為null，他們的得分都會被當做null處理
• playerId字段和score字段被指定為輸出：["$playerId"," $score"]，以數組的形式返回
• sortBy: { "score": -1 }指定了排序的方式，空值被排在最后，返回playerId數組

如下：

[{_id: "G1",playerId: [ [ "PlayerA", 1 ], [ "PlayerD", null ], [ "PlayerE", null ] ]}
]

BSON數據類型排序

當不同類型排序是，使用BSON數據類型的順序進行排序：

• 當進行正序排序時（由小到大），字符串的優先級在數值之前
• 當進行逆序排序時（由大到小），字符串的優先級在數值之前

下面的例子中包含了字符串和數值類型：

db.aggregate( [{$documents: [{ playerId: "PlayerA", gameId: "G1", score: 1 },{ playerId: "PlayerB", gameId: "G1", score: "2" },{ playerId: "PlayerC", gameId: "G1", score: "" }]},{$group:{_id: "$gameId",playerId: {$bottomN:{output: ["$playerId","$score"],sortBy: {"score": -1},n: 3}}}}
] )

在這個例子中：

• PlayerA的得分是整數1
• PlayerB的得分是字符串"2"
• PlayerC的得分是空字符串""

因為排序指定為逆序{ "score" : -1 }，字符串的字面量排在PlayerA的數值得分之前：

[{_id: "G1",playerId: [ [ "PlayerB", "2" ], [ "PlayerC", "" ], [ "PlayerA", 1 ] ]}
]

舉例

使用下面的命令創建gamescores集合：

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 }
])

查找三個得分最低的

使用$bottomN查找單個游戲中得分最低的3個：

db.gamescores.aggregate( [{$match : { gameId : "G1" }},{$group:{_id: "$gameId",playerId:{$bottomN:{output: ["$playerId", "$score"],sortBy: { "score": -1 },n:3}}}}
] )

本例中：

• 使用$match階段用一個gameId對結果進行篩選，即：G1
• 使用$group階段依據gameId對結果進行分組，本例中只有一個分組G1
• 使用output : ["$playerId"," $score"]為bottom指定輸出字段
• 使用sortBy: { "score": -1 }按照得分進行逆序排序
• 使用$bottomN返回游戲得分最低的3個選手和得分

結果如下：

[{_id: "G1",playerId: [ [ "PlayerB", 33 ], [ "PlayerA", 31 ], [ "PlayerD", 1 ] ]}
]

與下面的SQL查詢等價：

SELECT T3.GAMEID,T3.PLAYERID,T3.SCORE
FROM GAMESCORES AS GS
JOIN (SELECT TOP 3GAMEID,PLAYERID,SCOREFROM GAMESCORESWHERE GAMEID = "G1"ORDER BY SCORE) AS T3ON GS.GAMEID = T3.GAMEID
GROUP BY T3.GAMEID,T3.PLAYERID,T3.SCOREORDER BY T3.SCORE DESC

查找全部游戲中三個最低的得分

使用$bottomN查找所有游戲中得分最低的三個

db.gamescores.aggregate( [{$group:{ _id: "$gameId", playerId:{$bottomN:{output: [ "$playerId","$score" ],sortBy: { "score": -1 },n: 3}}}}
] )

在本例中：

• 使用$group按照groupId對結果排序
• 使用output : ["$playerId", "$score"]指定bottom輸出的字段
• 使用sortBy: { "score": -1 }按照得分進行逆序排序
• 使用$bottomN返回所有游戲中得分最低的三個

結果如下：

[{_id: "G1",playerId: [ [ "PlayerB", 33 ], [ "PlayerA", 31 ], [ "PlayerD", 1 ] ]},{_id: "G2",playerId: [ [ "PlayerC", 66 ], [ "PlayerB", 14 ], [ "PlayerA", 10 ] ]}
]

這個操作與下面的SQL語句等價：

SELECT PLAYERID,GAMEID,SCORE
FROM(SELECT ROW_NUMBER() OVER (PARTITION BY GAMEID ORDER BY SCORE DESC) AS GAMERANK,GAMEID,PLAYERID,SCOREFROM GAMESCORES
) AS T
WHERE GAMERANK >= 2
ORDER BY GAMEID

基于分組key來計算參數n

可以動態指定n的值，在本例中$cond表達式用在gameId字段：

db.gamescores.aggregate([{$group:{_id: {"gameId": "$gameId"},gamescores:{$bottomN:{output: "$score",n: { $cond: { if: {$eq: ["$gameId","G2"] }, then: 1, else: 3 } },sortBy: { "score": -1 }}}}}
] )

在本例中：

• 使用$group按照groupId對結果排序
• 使用output : "$score"指定$bottomN輸出的字段
• 如果gameId是G2則n為1，否則n為3
• 使用sortBy: { "score": -1 }按照得分進行逆序排序

操作結果如下：

[{ _id: { gameId: "G2" }, gamescores: [ 10 ] },{ _id: { gameId: "G1" }, gamescores: [ 33, 31, 1 ] }
]