I have a collection of User documents with the following shape:
// User
{
payments: [
{ total: number },
],
}
The payments array of nested documents of variable length contains number totals with values equal to or greater than 0.
How do I find a list of Users where the length of payments with total > 0 is greater than one?
For example, I want to find documents like these:
// two totals > 0 👍
{
payments: [
{ total: 50 },
{ total: 50 },
],
}
// two totals > 0 👍
{
payments: [
{ total: 50 },
{ total: 0 },
{ total: 50 },
],
}
// three totals > 0 👍
{
payments: [
{ total: 50 },
{ total: 50 },
{ total: 0 },
{ total: 50 },
],
}
but not these documents:
// not enough totals > 0 👎
{
payments: [
{ total: 50 },
{ total: 0 },
{ total: 0 },
],
}
// not enough totals > 0 👎
{
payments: [
{ total: 0 },
{ total: 0 },
],
}
Thank you for any help in advance!
Demo - https://mongoplayground.net/p/AG8Q1GqSEcl
You have to use an aggregation query.
$filter array where the total is greater than 0 and get the $size of filtered array. Check if it's greater than 1 $match
db.collection.aggregate([
{
$project: {
payments: "$payments",
paymentsGreaterThanZero: {
$size: {
$filter: { input: "$payments", as: "item", cond: { $gt: [ "$$item.total",0 ] } }
}
}
}
},
{ $match: { paymentsGreaterThanZero: { $gt: 1 } } },
{ $project: { payments: "$payments" } }
])