如何使用来自另一个集合的信息过滤MongoDB集合

Question

我有两个集合Accounts和Debts。我需要获得Accounts集合中"净节省"大于某个数字的项目。净储蓄是指从账户储蓄中减去所有债务后的储蓄。任何关于如何解决这个问题的建议都将非常有帮助。

账户收款：

{
"AccountNumber": "A1234",
"Savings": 1000
}
{
"AccountNumber": "A4567",
"Savings": 500
}

债务催收：

{
"AccountNumber": "A1234",
"Debt": 10,
"Lender": "A"
}
{
"AccountNumber": "A1234",
"Debt": 20,
"Lender": "B"
}
{
"AccountNumber": "A4567",
"Debt": 50,
"Lender": "B"
}

净储蓄>500 的账户

{
"AccountNumber": "A1234",
"NetSavings": 970
}

Answer 1

您可以首先从Accounts集合中找到帐号，然后将$in与Debits集合一起使用

const accountNumbers = (await Account.find({ Savings: { $gte: 500 }})).map(({ AccountNumber }) => AccountNumber)
const debits = await Debits.find({ AccountNumber: { $in: accountNumbers }})

或使用聚合

Accounts.aggregte([
{ $lookup: {
from: "debits",
localField: "AccountNumber",
foreignField: "AccountNumber",
as: "acc"
}},
{ $addFields: {
NetSavings: {
$subtract: ["$Savings", { $sum: "$acc.Debt" }]
}
}}
])