按ID将Mongo文档分组，并按时间戳获取最新文档 [英] Group Mongo documents by id and get the latest document by timestamp

问题描述

想象一下，我们在mongodb中存储了以下文档集:

Imagine we have the following set of documents stored in mongodb:

{ "fooId" : "1", "status" : "A", "timestamp" : ISODate("2016-01-01T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "1", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "1", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "2", "status" : "A", "timestamp" : ISODate("2016-01-01T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "2", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "3", "status" : "A", "timestamp" : ISODate("2016-01-01T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "3", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "3", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "3", "status" : "D", "timestamp" : ISODate("2016-01-04T00:00:00.000Z") "otherInfo" : "BAR", ... }

我想根据时间戳获取每个fooId的最新状态.因此，我的退货将如下所示:

I'd like to get the latest status for each fooId based on timestamp. Therefore, my return would look like:

{ "fooId" : "1", "status" : "C", "timestamp" : ISODate("2016-01-03T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "2", "status" : "B", "timestamp" : ISODate("2016-01-02T00:00:00.000Z") "otherInfo" : "BAR", ... }
{ "fooId" : "3", "status" : "D", "timestamp" : ISODate("2016-01-04T00:00:00.000Z") "otherInfo" : "BAR", ... }

我一直在尝试通过使用group运算符使用聚合来解决此问题，但是我想知道的部分是有一种简单的方法可以从聚合中获取整个文档，因此看起来就像我曾经使用过查找查询?看来您在分组时必须指定所有字段，并且如果文档上可能包含我不知道的可选字段，那么这似乎是不可扩展的.我当前的查询如下所示:

I've been trying to go about this by using aggregation using the group operator, but the part I'm wondering is there an easy way to get the whole document back from an aggregation so it looks the same as if I had used a find query? It seems you have to specify all the fields when you group, and that doesn't seem extensible if documents can have optional fields on them that may be unknown to me. The current query I have looks like this:

db.collectionName.aggregate(
   [
     { $sort: { timestamp: 1 } },
     {
       $group:
         {
           _id: "$fooId",
           timestamp: { $last: "$timestamp" },
           status: { "$last": "$status" },
           otherInfo: { "$last": "$otherInfo" },
         }
     }
   ]
)

推荐答案

如果要进行聚合，则需要执行与SQL类似的操作，这意味着指定每列的聚合操作，唯一的选择是使用$$ROOT运算符

If you are doing and aggregation, you need to do similar to SQL , which mean specify the aggregation operation per column, the only option you have is use the $$ROOT operator

db.test.aggregate(
   [
    { $sort: { timestamp: 1 } },
     {
       $group:
         {
           _id: "$fooId",
           timestamp: { $last: "$$ROOT" }
         }
     }
   ]
);

但这会稍微改变输出

{ "_id" : "1", "timestamp" : { "_id" : ObjectId("570e6be3e81c8b195818e7fa"), 
  "fooId" : "1", "status" : "A", "timestamp" :ISODate("2016-01-01T00:00:00Z"), 
  "otherInfo" : "BAR" } }

如果您想返回原始文档格式，则此后可能需要一个$ project阶段

If you want to return the original document format, you probably need a $project stage after that

这篇关于按ID将Mongo文档分组，并按时间戳获取最新文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！