I have a collection.

{ 
    "_id" : "410a7cb2-7ee1-4e7a-9fb7-fa651fcaa4e5", 
    "reqHistoryEvents" : [
        {
            "reqHistoryMsg" : "abcd", 
            "reqHistoryCreatedAt" : ISODate("2022-04-27T08:18:30.850 0000"), 
           
        }, 
        {
            "reqHistoryMsg" : "EFGH ", 
            "reqHistoryCreatedAt" : ISODate("2022-04-27T08:22:12.716 0000"), 
          
        }, 
        {
            "reqHistoryMsg" : "IJKL", 
            "reqHistoryCreatedAt" : ISODate("2022-04-27T08:22:12.716 0000"), 
        }
    ]
}

I want to convert it to this :::::

{ 
    "_id" : "410a7cb2-7ee1-4e7a-9fb7-fa651fcaa4e5", 
    "reqHistoryEvents" : [
        {
            "reqHistoryMsg" : "abcd", 
            "reqHistoryCreatedAt" : ISODate("2022-04-27T08:18:30.850 0000"), 
           
        }, 
        {
            "reqHistoryMsg" : ["EFGH ","IJKL"], 
            "reqHistoryCreatedAt" : ISODate("2022-04-27T08:22:12.716 0000"), 
          
        }
    ]
}

Basically it will be based on the creation Timestamp. We need to merge the reqHistoryMsg if we have same reqHistoryCreatedAt.

I am not able to write the mongo query. Any help?

CodePudding user response：

1. $unwind - Deconstruct reqHistoryEvents array field.
2. $group - Group by _id and reqHistoryEvents.reqHistoryCreatedAt fields. Add reqHistoryMsg into an array.
3. $group - Group by _id field and form reqHistoryEvents array.

db.collection.aggregate([
  {
    $unwind: "$reqHistoryEvents"
  },
  {
    $group: {
      _id: {
        _id: "$_id",
        reqHistoryCreatedAt: "$reqHistoryEvents.reqHistoryCreatedAt"
      },
      reqHistoryMsg: {
        $push: "$reqHistoryEvents.reqHistoryMsg"
      }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      reqHistoryEvents: {
        $push: {
          reqHistoryMsg: "$reqHistoryMsg",
          reqHistoryCreatedAt: "$_id.reqHistoryCreatedAt"
        }
      }
    }
  }
])

Sample Mongo Playground