Mongoose关联双集合聚合求和结果异常问题求助
2026-7-7
MongoDB聚合查询统计数值异常问题解决
集合Schema定义
Entity
const Entity = new Schema( { name: { type: String, required: true, }, type: { type: String, required: true, enum: ["owner", "customer", "supplier"], }, } );
Account
const Account = new Schema( { name: { type: String, required: true, }, phone: { type: String, default: "+964", }, } );
PR
const PR = new Schema( { number: { type: Number, default: 0, }, customer: { type: Schema.Types.ObjectId, ref: "Entity", }, owner: { type: Schema.Types.ObjectId, ref: "Entity", }, account: { type: Schema.Types.ObjectId, ref: "Account", }, items: [item.schema], total_usd: { type: Number, default: 0, }, total_iqd: { type: Number, default: 0, }, } );
Invoice
const Invoice = new Schema( { number: { type: Number, index: true, default: 0, }, customer: { type: Schema.Types.ObjectId, ref: "Entity", }, owner: { type: Schema.Types.ObjectId, ref: "Entity", }, account: { type: Schema.Types.ObjectId, ref: "Account", }, total_usd: { type: Number, default: 0, }, total_iqd: { type: Number, default: 0, }, } );
需求说明
按状态、年份和account分组，统计PR和Invoice集合中total_usd与total_iqd的总和。
现有聚合查询代码
await entityModel .aggregate([ { $match: { _id: new mongoose.Types.ObjectId(req.query._id) } }, { $lookup: { from: "invoices", localField: "_id", foreignField: "customer", as: "invoices", }, }, { $unwind: "$invoices" }, { $lookup: { from: "accounts", localField: "invoices.account", foreignField: "_id", pipeline: [{ $project: { _id: 0, name: 1 } }], as: "invoices.account", }, }, { $unwind: "$invoices.account" }, { $lookup: { from: "prs", localField: "_id", foreignField: "customer", as: "prs", }, }, { $unwind: "$prs" }, { $lookup: { from: "accounts", localField: "prs.account", foreignField: "_id", pipeline: [{ $project: { _id: 0, name: 1 } }], as: "prs.account", }, }, { $unwind: "$prs.account" }, { $group: { _id: { inv_status: "$invoices.status", inv_account: "$invoices.account", inv_year: { $year: "$invoices.created_at" }, pr_status: "$prs.status", pr_account: "$prs.account", pr_year: { $year: "$prs.created_at" }, }, inv_sum_usd: { $sum: "$invoices.total_usd" }, inv_sum_iqd: { $sum: "$invoices.total_iqd" }, pr_sum_usd: { $sum: "$prs.total_usd" }, pr_sum_iqd: { $sum: "$prs.total_iqd" }, }, }, ]) .then((result) => { res.send(result); });
错误查询结果
[ { "_id": { "inv_status": "approved", "inv_account": { "name": "FIIQ" }, "inv_year": 2023, "pr_status": "approved", "pr_account": { "name": "FIIX" }, "pr_year": 2023 }, "inv_sum_usd": 480000, "inv_sum_iqd": 0, "pr_sum_usd": 514440, "pr_sum_iqd": 0 }, { "_id": { "inv_status": "approved", "inv_account": { "name": "FIIQ" }, "inv_year": 2023, "pr_status": "approved", "pr_account": { "name": "FIIQ" }, "pr_year": 2023 }, "inv_sum_usd": 144000, "inv_sum_iqd": 0, "pr_sum_usd": 40700, "pr_sum_iqd": 14256000 }, { "_id": { "inv_status": "approved", "inv_account": { "name": "FIIX" }, "inv_year": 2023, "pr_status": "approved", "pr_account": { "name": "FIIX" }, "pr_year": 2023 }, "inv_sum_usd": 2551740, "inv_sum_iqd": 0, "pr_sum_usd": 2829420, "pr_sum_iqd": 0 }, { "_id": { "inv_status": "approved", "inv_account": { "name": "FIIX" }, "inv_year": 2023, "pr_status": "approved", "pr_account": { "name": "FIIQ" }, "pr_year": 2023 }, "inv_sum_usd": 765522, "inv_sum_iqd": 0, "pr_sum_usd": 223850, "pr_sum_iqd": 78408000 } ]
问题原因及修复方案
问题根源
原查询先对invoices执行$unwind，再关联prs并执行$unwind，导致笛卡尔积：每个Invoice会和所有关联的PR进行组合，最终求和时同一Invoice/PR的数值被重复计算，结果远大于实际值。
修复方案
分别对Invoice和PR集合在$lookup阶段完成分组统计，避免笛卡尔积。具体步骤：
- 匹配目标Entity；
- 用
$lookup关联invoices，在pipeline内完成按状态、年份、account的分组求和，并关联Account名称； - 用
$lookup关联prs，同样在pipeline内完成分组求和与Account关联； - 最后整理输出格式。
修复后的代码
await entityModel .aggregate([ { $match: { _id: new mongoose.Types.ObjectId(req.query._id) } }, // 处理Invoice的分组统计 { $lookup: { from: "invoices", let: { entityId: "$_id" }, pipeline: [ { $match: { $expr: { $eq: ["$customer", "$$entityId"] } } }, // 关联Account获取名称 { $lookup: { from: "accounts", localField: "account", foreignField: "_id", as: "account_info", pipeline: [{ $project: { name: 1 } }] } }, { $unwind: "$account_info" }, // 按状态、年份、account分组求和 { $group: { _id: { status: "$status", year: { $year: "$created_at" }, account: "$account_info.name" }, sum_usd: { $sum: "$total_usd" }, sum_iqd: { $sum: "$total_iqd" } } }, // 重命名字段方便识别 { $project: { _id: 0, type: "invoice", status: "$_id.status", year: "$_id.year", account: "$_id.account", total_usd: "$sum_usd", total_iqd: "$sum_iqd" } } ], as: "invoice_stats" } }, // 处理PR的分组统计 { $lookup: { from: "prs", let: { entityId: "$_id" }, pipeline: [ { $match: { $expr: { $eq: ["$customer", "$$entityId"] } } }, { $lookup: { from: "accounts", localField: "account", foreignField: "_id", as: "account_info", pipeline: [{ $project: { name: 1 } }] } }, { $unwind: "$account_info" }, { $group: { _id: { status: "$status", year: { $year: "$created_at" }, account: "$account_info.name" }, sum_usd: { $sum: "$total_usd" }, sum_iqd: { $sum: "$total_iqd" } } }, { $project: { _id: 0, type: "pr", status: "$_id.status", year: "$_id.year", account: "$_id.account", total_usd: "$sum_usd", total_iqd: "$sum_iqd" } } ], as: "pr_stats" } }, // 合并两个统计结果数组 { $project: { combined: { $concatArrays: ["$invoice_stats", "$pr_stats"] } } }, { $unwind: "$combined" }, // 按状态、年份、account、类型分组 { $group: { _id: { type: "$combined.type", status: "$combined.status", year: "$combined.year", account: "$combined.account" }, total_usd: { $sum: "$combined.total_usd" }, total_iqd: { $sum: "$combined.total_iqd" } } }, // 整理输出格式 { $project: { _id: 0, document_type: "$_id.type", status: "$_id.status", year: "$_id.year", account: "$_id.account", total_usd: 1, total_iqd: 1 } } ]) .then((result) => { res.send(result); });
说明
- 该方案在
$lookup的pipeline内完成子集合的分组统计，彻底避免了笛卡尔积； - 最终结果会按文档类型（invoice/pr）、状态、年份、account展示统计总和，数值与实际数据一致；
- 若需要将Invoice和PR的同维度数据合并，可调整最后一步的
$group逻辑。
内容的提问来源于stack exchange，提问作者Mohammed Riyadh
相关产品推荐
相关产品推荐
