I have a collection with array field:
{[
name:String
buyPrice:Int
sellPrice:Int
]}
I am trying to find min and max buy/sell price. At some entries buy or sell price is zero so I need to find min price but greater than zero. Here is my query:
{
name: '$_id',
maxBuyPrice: {
$max: '$commodities.buyPrice'
},
minBuyPrice: {
$min: '$commodities.buyPrice'
},
maxSellPrice: {
$max: '$commodities.sellPrice'
},
minSellPrice: {
$min: '$commodities.sellPrice'
}
}
I can't use $match operator with $gt because I can lost entries with max values
$filter to iterate loop of commodities.buyPrice array and get filtered price that is greater than 0$min in above filtered result$ifNull to check if above $min result is null then return only 0 {
$project: {
name: "$_id",
maxBuyPrice: { $max: "$commodities.buyPrice" },
minBuyPrice: {
$ifNull: [
{
$min: {
$filter: {
input: "$commodities.buyPrice",
cond: { $gt: ["$$this", 0] }
}
}
},
0
]
},
maxSellPrice: { $max: "$commodities.sellPrice" },
minSellPrice: {
$ifNull: [
{
$min: {
$filter: {
input: "$commodities.sellPrice",
cond: { $gt: ["$$this", 0] }
}
}
},
0
]
}
}
}