node.js - MongoDB : How to pick with $lte/lt all the documents that answer to specific $sum condition?

Question

考虑:

 EightWeekGamePlan.aggregate(
    [
      { $match: { LeadId: { $in: leads }, Week: week, 
          // total: { $lt: 5 } // This part doesn't work
        } },
      {
        $group: {
          _id: {
            LeadId: "$LeadId",
            total: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
          }
        }
      }
    ]

如何挑选 $TotalClaimsLeftToBeClaimedByClientType 总和小于 5 的所有文档？

我尝试过 total: { $lt: 5 }但我得到了一个空数组。

这是架构:

const mongoose = require("mongoose");
const Schema = mongoose.Schema;
const EightWeekGamePlanSchema = new Schema({
  Week: {
    type: Number,
    required: true
  },
  LeadId: {
    type: String,
    required: true
  },
  PackageId: {
    type: String,
    required: true
  },
  BusinessName: {
    type: String,
    required: true
  },
  PhoneNumberMasque: {
    type: String,
    required: true
  },
  City: {
    type: String,
    required: true
  },
  Rooms: {
    type: Number,
    required: true
  },
  LeadStartDate: {
    type: Date
  },
  LeadEndDate: {
    type: Date
  },

  TargetedToBeClaimedByClientType: {
    type: Number,
    required: true
  },
  TotalClaimsLeftToBeClaimedByClientType: {
    // incresed by 1 every time it's claimed
    type: Number,
    required: true
  },
  TotalClaimsToBeClaimedByClientType: {
    // Stays fixed
    type: Number,
    required: true
  },
  Status: {
    type: Number,
    required: true
  },

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

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

Answer 1

根据您的查询，尝试以下操作:

EightWeekGamePlan.aggregate(
    [
        {
            $match: {
                LeadId: { $in: leads }, Week: week
            }
        },
        {
            $group: {
                _id: {
                    LeadId: "$LeadId",
                    total: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
                }
            }
        }, {
            $match: {
                 '_id.total': { $lte: 5 }
            }
        }
    ])

从上面来看，$match不起作用，因为您的total不是顶级字段，它位于_id内部。因此它基本上是根据 LeadId + TotalClaimsLeftToBeClaimedByClientType 之和 进行分组的。以防万一，如果您只想根据 LeadId 进行分组，请检查下面的一项。

(或者)您可以更改查询:

EightWeekGamePlan.aggregate(
    [
        {
            $match: {
                LeadId: { $in: leads }, Week: week
            }
        },
        {
            $group: {
                _id: {
                    LeadId: "$LeadId"
                },
                total: { $sum: "$TotalClaimsLeftToBeClaimedByClientType" }
            }
        }, {
            $match: {
                'total': { $lte: 5 }
            }
        }
    ])