Mongodb Aggregate Nested Group with Recent Updated Document

Question

I have assigned person, status inside my collection simply like below.

[
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411771", "STATUS": 1, "UPDATE_DATE": ISODate("2016-12-21T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411772", "STATUS": 4, "UPDATE_DATE": ISODate("2016-12-22T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411774", "STATUS": 4, "UPDATE_DATE": ISODate("2016-12-23T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411774", "STATUS": 3, "UPDATE_DATE": ISODate("2016-12-24T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322311", "SPEC_ID" : "58411775", "STATUS": 1, "UPDATE_DATE": ISODate("2016-12-25T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322311", "SPEC_ID" : "58411779", "STATUS": 3, "UPDATE_DATE": ISODate("2016-12-23T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322322", "SPEC_ID" : "58411777", "STATUS": 1, "UPDATE_DATE": ISODate("2016-12-20T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322322", "SPEC_ID" : "58411778", "STATUS": 4, "UPDATE_DATE": ISODate("2016-12-21T04:10:23.000Z")}
]

i want to group this data by ASSIGN_ID and inside that by STATUS count of each STATUS and recent updated SPEC_ID by UPDATE_DATE. same as below. (Expected Result)

[  
   {  
      "ASSIGN_ID":"583f84bce58725f76b322398",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":2
         }
      ],
      "SPEC_ID": "58411774"
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322311",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         }
      ],
      "SPEC_ID": "58411775"
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322322",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":1
         }
      ],
      "SPEC_ID": "58411778"
   }
]

however, i was able to write the nested group by STATUS and ASSIGN_ID. unable to get the recent SPEC_ID. please find the query below.

            Modal.aggregate([
              {"$group": {
                "_id": {
                    "INSPECTED_BY": "$INSPECTED_BY",
                    "STATUS": "$STATUS"
                },
                "total": { "$sum": 1 }
              }},
              {"$group": {
                "_id": "$_id.INSPECTED_BY",
                "data": { "$push": {
                    "STATUS": "$_id.STATUS",
                    "total": "$total"
                }}
              }}]);

and it return a data set like below.

[  
   {  
      "ASSIGN_ID":"583f84bce58725f76b322398",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":2
         }
      ],
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322311",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         }
      ],

   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322322",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":1
         }
      ],
   }
]

Answer 1

You need to $sort the documents getting into the pipeline first then in your $group stage return the latest SPEC_ID field for each group with the $first accumulator.

Running the following pipeline should give your the desired result:

Model.aggregate([
    { "$sort": { "ASSIGN_ID": 1, "STATUS": 1, "UPDATE_DATE": -1 } },
    {
        "$group": {
            "_id": {
                "ASSIGN_ID": "$ASSIGN_ID",                
                "STATUS" : "$STATUS",
            },
            "SPEC_ID": { "$first": "$SPEC_ID" },
            "COUNT": { "$sum": 1 }
        }
    },
    {
        "$group": {
            "_id": "$_id.ASSIGN_ID",
            "STATUS_GROUP": { 
                "$push": {
                    "STATUS": "$_id.STATUS",
                    "COUNT": "$COUNT"
                }
            },
            "SPEC_ID": { "$first": "$SPEC_ID" }
        }
    }
]).exec(callback);