按 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屋！