I am currently implementing a project pipeline that involves filtering values greater than 100 from fields within an object that is part of an array. Here's an example of the database structure:
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
}
THIS IS MY CURRENT 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 }
}
}
}
}
The expected output should be:
{
_id: 1,
taxID: aldsfkjasdlñfk,
invoicesAmountGreaterThanOneHundred: 350
}
I am using MongoDB version 3.6.3.
In the future, I also plan to incorporate "invoicesAmountLesserThanOneHundred" following a similar approach for values lesser than 100.