MongoDB mgo汇总最早的创建日期和最近的最后修改日期

Question

I am trying to retrieve the earliest created date and latest last modified date for each object_name in the following data

{ "_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") }

I have no problem to group distinct object_name with each username with the following pipeline

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

However I also want to add earliest created date and latest last modified date for each object.

Here is my desired output:

[
    {
        "_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"
            }
        ]
    }
]

I think it has something to do with $sort and $first or $last but I don't know how to put them together.

Answer 1

You can try below aggregation query.

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"
    }
  }
}}])