MongoDB mgo 聚合最早创建日期和最新最后修改日期

Question

我正在尝试检索以下数据中每个 object_name 的最早创建日期和最后修改日期

{ "_id" : ObjectId("5a510666b2e543371cff44ef"), "object_name" : "A", "username" : "user1", "created_at" : ISODate("2018-01-06T17:24:54.026Z"), "last_modified" : ISODate("2018-01-06T17:24:54.026Z") }
{ "_id" : ObjectId("5a5106e7b2e543371cff4515"), "object_name" : "A", "username" : "user1", "created_at" : ISODate("2018-01-06T17:27:03.262Z"), "last_modified" : ISODate("2018-01-06T17:27:03.262Z") }
{ "_id" : ObjectId("5a510933b2e543371cff45be"), "object_name" : "B", "username" : "user1", "created_at" : ISODate("2018-01-06T17:36:51.300Z"), "last_modified" : ISODate("2018-01-06T17:36:51.300Z") }
{ "_id" : ObjectId("5a510939b2e543371cff45c5"), "object_name" : "C", "username" : "user2", "created_at" : ISODate("2018-01-06T17:36:57.058Z"), "last_modified" : ISODate("2018-01-06T17:36:57.058Z") }

我可以通过以下管道将不同的 object_name 与每个用户名分组

pipeline := []bson.M{
    {"$group": bson.M{"_id": "$username", "objects": bson.M{"$addToSet": "$object_name"}}},
}

不过，我还想为每个对象添加最早的创建日期和最近的最后修改日期。

这是我想要的输出:

[
    {
        "_id": "user1",
        "objects": [
            {
                "object_name": "A",
                "created_at": "2018-01-06T17:24:54.026Z",
                "last_modified": "2018-01-06T17:27:03.262Z"
            },
            {
                "object_name": "B",
                "created_at": "2018-01-06T17:36:51.300Z",
                "last_modified": "2018-01-06T17:36:51.300Z"
            }
        ]
    },
    {
        "_id": "user2",
        "objects": [
            {
                "object_name": "C",
                "created_at": "2018-01-06T17:36:57.058Z",
                "last_modified": "2018-01-06T17:36:57.058Z"
            }
        ]
    }
]

我认为它与 $sort 和 $first 或 $last 有关，但我不知道如何将它们放在一起。

Answer 1

您可以尝试下面的聚合查询。

db.colname.aggregate([
{"$group":{
  "_id":{
    "username":"$username",
    "object_name":"$object_name"
  },
  "created_at":{"$min":"$created_at"},
  "last_modified":{"$max":"$last_modified"}
}},
{"$group":{
  "_id":"$_id.username",
  "objects":{
    "$push":{
      "object_name":"$_id.object_name",
      "created_at":"$created_at",
      "last_modified":"$last_modified"
    }
  }
}}])