我是一个初学者。我正试图根据特定字段获取最后的记录。在SQLselect * from powerandlevel group by deviceId order by desc limit 1
。因此,在这个基于deviceId的SQL查询中,我可以获得每个deviceId的最后记录。我也想在mongodb
[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:35:30.615Z"
deviceId: "48701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]
我使用这个查询db.imageEvent.aggregate([{ "$group" : {"_id" : { "deviceId" : "$deviceId"}}}, {$sort: {eventTime: 1}}, { $limit : 1 }])
结果
[{ "_id" : { "deviceId" : "58701ED21819" } },
{ "_id" : { "deviceId" : "48701ED21819" } }]
期待这样的结果
[{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T07:38:10.543Z"
deviceId: "58701ED21819"
},
{
_id: "ObjectId(6246ab45e95eac6c85726cfc)"
imageName: "IMG_123456.jpg"
eventTime: "2022-04-01T08:05:50.865Z"
deviceId: "48701ED21819"
}]
如果您不需要每个结果的ObjectId
,只需要通用值和最新日期,您可以简化为:
db.imageEvent.aggregate([
{
$group: {
_id: "$deviceId",
imageName: {$first: "$imageName"},
eventTime: {$max: "$eventTime"},
deviceId: {$first: "$deviceId"},
}
}
])
正如你在操场上看到的对于组中相同的值,$first
是方便的。$max
会告诉你最晚的日期。
这将防止您对整个集合进行排序,并在查询期间创建大型文档,因为文档有一个上限大小。
$sort
-按eventTime
DESC排序。$group
-按deviceId
分组,并通过$first
将第一个文档放入data
字段。$replaceRoot
-将输入文档替换为data
db.imageEvent.aggregate([
{
$sort: {
eventTime: -1
}
},
{
"$group": {
"_id": {
"deviceId": "$deviceId"
},
data: {
$first: "$$ROOT"
}
}
},
{
"$replaceRoot": {
"newRoot": "$data"
}
}
])
Mongo Playground示例