时间戳

首页 > 解决方案 > MongoDB：如何使用 $lte/lt 选择所有符合特定 $sum 条件的文档？

node.js - MongoDB：如何使用 $lte/lt 选择所有符合特定 $sum 条件的文档？

问题描述

考虑 ：

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

标签： node.jsmongodb

解决方案

根据您的查询，试试这个：

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

从上面它$match不起作用，因为你total不是顶级字段，它在里面_id。所以它基本上是基于LeadId + sum of TotalClaimsLeftToBeClaimedByClientType. 以防万一您只想根据LeadId以下一项进行分组。

（或）您可以更改查询：

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

推荐阅读