Mongodb aggregation - min but not zero

user_12907709 0
Viewed 405

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

aggregation-framework mongodb
1 Answers
  • $filter to iterate loop of commodities.buyPrice array and get filtered price that is greater than 0
  • apply $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
        ]
      }
    }
  }

Playground

user_89871280
Related