javascript - Mongodb 聚合嵌套组与最近更新的文档

Question

我已经在我的收藏中分配了人员、状态，如下所示。

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

我想按ASSIGN_ID 对这些数据进行分组，并在其中按每个STATUS 的STATUS 计数和最近更新的SPEC_ID UPDATE_DATE。同下。 (预期结果)

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

但是，我能够通过STATUS 和ASSIGN_ID 编写嵌套组。无法获取最近的 SPEC_ID。请在下面找到查询。

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

它返回如下数据集。

[  
   {  
      "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

您需要 $sort 文档首先进入管道，然后进入您的 $group 阶段返回每个组的最新 SPEC_ID 字段，带有 $first 蓄能器。

运行以下管道应该会得到您想要的结果:

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);