Mongo有条件地求和项目管道中的值

Question

我试图在一个值大于100的项目管道中添加值，这些值是数组中的字段和对象。我有这样的东西：

数据库：

---客户集合-

client: {
_id: 1,
taxID: aldsfkjasdlñfk
// other stuff
}

---发票收款-

invoice: {
_id: 1,
clientID: 1,
total: 50
},
invoice: {
_id: 2,
clientID: 1,
total: 150
},
invoice: {
_id: 3,
clientID: 1,
total: 200
}

这是我的问题：

{
$lookup: {
from: 'invoices',
localField: '_id',
foreignField: 'client.id',
as: 'invoices'
}
},
{
$project: {
id: 1,
taxID: aldsfkjasdlñfk,
invoicesAmountGreaterThanOneHundred: {
$sum: {
$cond: { if: { $gte: ['$invoices.total', 100] }, then: '$invoices.total', else: 0 }
}
}
}
}

所以输出应该是：

{
_id: 1.
taxID: aldsfkjasdlñfk,
invoicesAmountGreaterThanOneHundred: 350
}

我使用的是Mongo 3.6.3。

同样在将来，我将添加一个"；发票金额小于一百"；，同样的方法，但当然少于100。

Answer 1

在$sum之前使用$filter

db.client.aggregate([
{
$lookup: {
from: "invoices",
localField: "_id",
foreignField: "clientID",
as: "invoices"
}
},
{
$set: {
"invoices": {
"$filter": {
"input": "$invoices",
"as": "i",
"cond": { $gte: [ "$$i.total", 100 ] }
}
}
}
},
{
$project: {
id: 1,
taxID: 1,
invoicesAmountGreaterThanOneHundred: {
$sum: "$invoices.total"
}
}
}
])

mongoplayground

---

使用$reduce

db.client.aggregate([
{
$lookup: {
from: "invoices",
localField: "_id",
foreignField: "clientID",
as: "invoices"
}
},
{
$set: {
"invoicesAmountGreaterThanOneHundred": {
$reduce: {
input: "$invoices",
initialValue: "",
in: {
$sum: [
"$$value",
{
$cond: {
if: { $gte: [ "$$this.total", 100 ] },
then: "$$this.total",
else: 0
}
}
]
}
}
}
}
}
])

mongoplayground