获取具有不同标准的最后一个文档 [英] Get last documents with a distinct criteria
问题描述
我在想出一种执行某个 MongoDb 查询的好方法时遇到了麻烦.首先,这是我想做的查询类型.假设有一个简单的数据库,它通过电子刷卡来记录进入和退出事件(以及可能的其他操作,无关紧要).所以有一个名为 swipelog
的集合,其中包含如下所示的简单文档:
I'm having trouble coming up with a good way to do a certain MongoDb query. First, here's what kind of query I want to do. Assume a simple database which logs entry and exit events (and possibly other actions, doesn't matter) by electronic card swipe. So there's a collection called swipelog
with simple documents which look like this:
{
_id: ObjectId("524ab4790a4c0e402200052c")
name: "John Doe",
action: "entry",
timestamp: ISODate("2013-10-01T1:32:12.112Z")
}
现在我想列出姓名及其上次输入时间(以及我可能需要的任何其他字段,但下面的示例仅使用这两个字段).
Now I want to list names and their last entry times (and any other fields I may want, but example below uses just these two fields).
这是我现在所拥有的,作为 MongoDb JavaScript 控制台的单行":
Here is what I have now, as a "one-liner" for MongoDb JavaScript console:
db.swipelog.distinct('name')
.forEach( function(name) {
db.swipelog.find( { name: name, action:"entry" } )
.sort( { $natural:-1 } )
.limit(1)
.forEach( function(entry) {
printjson( [ entry.name, entry.timestamp ] )
})
})
打印如下内容:
[ "John Doe", ISODate("2013-10-01T1:32:12.112Z")]
[ "Jane Deo", ISODate("2013-10-01T1:36:12.112Z")]
...
问题
我认为上面有明显的缩放问题.如果有一百个名字,那么将对数据库进行 1+100 次查询.那么获取每个不同 name
的最后一个 timestamp
"的好/正确方法是什么?改变数据库结构或添加一些集合是可以的,如果它能让这更容易.
Question
I think above has the obvious scaling problem. If there are a hundred names, then 1+100 queries will be made to the database. So what is a good/correct way to get "last timestamp
of every distinct name
" ? Changing database structure or adding some collections is ok, if it makes this easier.
推荐答案
你可以使用聚合框架来实现:
You can use aggregation framework to achieve this:
db.collection.aggregate(
[
{$match:
{action:'entry'}
},
{$group:
{_id:'$name',
first:
{$max:'$timestamp'}
}
}
])
如果您可能在结果中包含其他字段,您可以使用 $first 运算符
If you likely to include other fields in the results, you can use the $first operator
db.collection.aggregate(
[
{$match:
{action:'entry'}
},
{$sort:
{name:1, timestamp:-1}
},
{$group:
{_id:'$name',
timestamp: {$first:'$timestamp'},
otherField: {$first:'$otherField'},
}
}
])
这篇关于获取具有不同标准的最后一个文档的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!