I'm trying to add in a project pipeline where the values are greater than 100, the values are fields inside and object which are part of an array. I have something like this:
Database:
---Clients Collection---
client: {
_id: 1,
taxID: aldsfkjasdlñfk
// other stuff
}
---Invoices Collection---
invoice: {
_id: 1,
clientID: 1,
total: 50
},
invoice: {
_id: 2,
clientID: 1,
total: 150
},
invoice: {
_id: 3,
clientID: 1,
total: 200
}
AND THIS IS MY QUERY:
{
$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 }
}
}
}
}
So the output should be:
{
_id: 1.
taxID: aldsfkjasdlñfk,
invoicesAmountGreaterThanOneHundred: 350
}
I'm using Mongo 3.6.3.
Also in the future I will add a "invoicesAmountLesserThanOneHundred", same method, but for lesser than 100 of course.
use $filter before $sum
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"
}
}
}
])
use $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
}
}
]
}
}
}
}
}
])