node.js - 在MongoDB中再次聚合+ $ project + $ group + $ sum + $ count + $ group

考虑架构：

const mongoose = require("mongoose");
const Schema = mongoose.Schema;
const EightWeekGamePlanSchema = new Schema({
  Week: {
    type: Number,
    required: true
  },
  LeadId: {
    type: String,
    required: true
  },
  SupplierGeneralId: {
    type: String,
    required: true
  },


  // ... More properties


  TotalClaimsLeftToBeClaimedByClientType: {
    // incresed by 1 every time it's claimed
    type: Number,
    required: true
  },



  InsertDate: {
    type: Date
    // default: Date.now
  }
});

module.exports = EightWeekGamePlan = mongoose.model(
  "eightweekgameplan",
  EightWeekGamePlanSchema
);

考虑一下Mongo查询：

   EightWeekGamePlan.aggregate(
      [
        // 1.Group by supplier
        // 2.Within each supplier group by month & year
        // Use count & sum
        {
          $project: {
            month: { $month: "$InsertDate" },
            year: { $year: "$InsertDate" }
          }
        },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: "$month",
              year: "$year"
            },
            ClaimsSummary : { $sum: "$TotalClaimsLeftToBeClaimedByClientType" } ,
            // TotalLeadsPerSupplierAndDate :  ...

            // Here I want to group again , by LeadID and count all the
            // unique LeadID's
          }
        }
       ]

我想按SupplierGeneralId和Month + Year的InsertDate分组，
总结每个月的TotalClaimsLeftToBeClaimedByClientType
再次分组，但是这次按LeadID进行分组，并计算每个供应商的所有唯一LeadId（先前按SupplierGeneralId，Month，Year分组）。


但是我越来越


  [{_id：{月份：1，年份：2020}，ClaimsSummary：0}]


...即使有数据

管道出了什么问题？如何重新分组以获取每个供应商的唯一LeadId？

谢谢

编辑：

我已经向Project添加了更多字段，但是现在我在$push中得到了空数组：

   EightWeekGamePlan.aggregate(
      [
        // 1.Group by supplier
        // 2.Within each supplier group by month & year
        // Use count & sum
        { $sort: { SupplierGeneralId: 1 } },
        {
          $project: {
            month: { $month: "$InsertDate" },
            year: { $year: "$InsertDate" },
            SupplierGeneralId: "$SupplierGeneralId",
            TotalClaimsLeftToBeClaimedByClientType:
              "$TotalClaimsLeftToBeClaimedByClientType"
          }
        },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: "$month",
              year: "$year"
            },
            LeadsCollection: {
              $push: {
                LeadId: "$LeadId"
              }
            },
            ClaimsSummary: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        }
        ]

输出：

[
[0]   {
[0]     _id: {
[0]       SupplierGeneralId: 'qCwHWFD1cBvrfPp5hdBL6M',
[0]       month: 1,
[0]       year: 2020
[0]     },
[0]     LeadsCollection: [
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {},
[0]       {}, {}, {}, {}, {}, {}, {}, {}
[0]     ],
[0]     ClaimsSummary: 4
[0]   }
[0] ]

第二编辑：

  EightWeekGamePlan.aggregate(
      [
        { $sort: { SupplierGeneralId: 1 } },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: { $month: "$InsertDate" },
              year: { $year: "$InsertDate" }
            },
            LeadsUploaded: {
              $push: {
                LeadId: "$LeadId"
              }
            },
            Sells: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        },
        {
          $project: {
            Sells: "$Sells",
            LeadsUploaded: {
              $reduce: {
                input: { $setUnion: "$LeadsUploaded.LeadId" },
                initialValue: [],
                in: {
                  $concatArrays: [
                    "$$value",
                    [
                      {
                        Lead_ID: "$$this"
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      ]

最佳答案

您应该放下$project阶段，然后立即进行分组，因此没有实际意义，添加它只会使管道效率降低。

将管道重写为：

EightWeekGamePlan.aggregate(
      [
        // 1.Group by supplier
        // 2.Within each supplier group by month & year
        // Use count & sum
        { $sort: { SupplierGeneralId: 1 } },
        {
          $group: {
            _id: {
              SupplierGeneralId: "$SupplierGeneralId",
              month: {"$month" : "$InsertDate"},
              year: { $year: "$InsertDate" },
            },
            LeadsCollection: {
              $push: {
                LeadId: "$LeadId"
              }
            },
            ClaimsSummary: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        }
        ]