获取具有不同条件的最新文档 [英] 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屋！