I have this mongoose schema.
const storeSchema = mongoose.Schema({
name: {
type: String,
required: true,
},
fruits: {
type: [String],
required: true,
},
});
And I need to get all the objects that have 2 or more bananas in the fruits array, like this.
{
"name": "Beautiful Store",
"fruits": ["banana", "apple", "banana", "pear"]
}
I was trying something like this, but with no success...
const query = await StoreModel.find({ fruits: { $gd: 2 } })
Somethign like this:
Option 1: ( less efective but intuitive )
db.collection.aggregate([
{
$addFields: {
numBananas: {
$size: {
$filter: {
input: "$fruits",
as: "item",
cond: {
$eq: [
"$$item",
"banana"
]
}
}
}
}
}
},
{
$match: {
numBananas: {
$gte: 2
}
}
},
{
$project: {
name: 1,
fruits: 1
}
}
])
Explained:
Option 2: ( Best , inspired by Buzz below :) )
db.collection.aggregate([
{
$match: {
$expr: {
$gte: [
{
$size: {
$filter: {
input: "$fruits",
as: "item",
cond: {
$eq: [
"$$item",
"banana"
]
}
}
}
},
2
]
}
}
},
{$sort:{name:1}}
])
Explained: Just match those having >=2 bananas , best if index created on fruits ( I think this solution is in 50/50 performance competition with the $reduce option from Buzz below , I would bet on this one )
Here are two variations on the theme.
The first uses $reduce to directly count the number of bananas (essentially, the {$size:{$filter...}} as above. For extra performance, we bring the calc and the $match together into a single stage:
db.foo.aggregate([
{$match: {$expr: {$gte: [
{$reduce: {
input: '$fruits',
initialValue: 0,
in: {$cond:[{$eq: ['$$this','banana']}, {$add:['$$value',1]}, '$$value']}
}}, 2]}
}}
]);
The second is a general purpose fruit counter in case you want to have more complex expressions, e.g. >=2 bananas or (1 pear and 1 apple). It does come at a cost of $unwind/$group.
c=db.foo.aggregate([
{$unwind: '$fruits'}
,{$group: {_id: {id: '$_id', f: '$fruits'}, N: {$sum:1}}}
,{$match: {'_id.f': 'banana', N:{$gte:2}} }
]);