How to add conditions inside $lte?

Question

I have a collection:

   @Prop({ type: Number, required: true, default: 0 })
   have:number;
   @Prop({ type: Boolean, required: false, default: null })
   unstable?: boolean;
   @Prop({ type: Number, required: false, default: null })
   max?: number;

I need to filter the selection according to this principle. If unstable === true, then do not select, if it is equal to false or null, then ignore the filter The second have condition must be less than max, but only if max !== null otherwise ignore this filter

Also, both of these conditions must pass, i.e. if the record does not match for some condition, then we do not add it to the selection

Everything is clear with the first condition, it's easy to check if unstable !== true, then ignore But in the second case, I can not normally design a filter. I think I'm doing everything right, but it doesn't work.

In general, this is what I came up with

       {
         $and:[
           {
             unstable: {
               $ne: true
             },
           },
           {
             have: {
               $lte: {
                 cond: { // tried to write $cond as well, doesn't swear at anything, but there is no result
                   if: {
                     max: { $eq: null },
                   },
                   then: Infinity,
                   else: '$max', // tried to use Infinity here too, silence too
                 },
               },
             },
           },
         ],
       }

my logic is like this:

have <= (max === null ? Infinity : max)

also tested this option and it works

have: {
   $lte: Infinity
}

p.s. Data example

  {
    id: 1,
    max: 10,
    have: 1,
    unstable: null,
  },
  {
    id: 2,
    max: 10,
    have: 10,
    unstable: null,
  },
  {
    id: 3,
    max: null,
    have: 1,
    unstable: null,
  },
  {
    id: 4,
    max: null,
    have: 1,
    unstable: true,
  },

expected output ids: 1 and 3

id 2 not included, because have >== max id 4 not included, because unstable === true

p.s.s. https://mongoplayground.net/p/MriDNX6U7El

Answer 1

You can try this one:

db.collection.find({
   unstable: { $ne: true },
   $expr: {
      $cond: {
         if: { $eq: ["$max", null] },
         then: true,
         else: { $lte: ["$have", "$max"] }
      }
   }
})

Or a bit shorter:

db.collection.find({
   unstable: { $ne: true },
   $expr: { $lte: ["$have", { $ifNull: ["$max", MaxKey()] }] }
})

Maybe MaxKey is not available in your client, then you can use

db.collection.find({
   unstable: { $ne: true },
   $expr: { $lte: ["$have", { $ifNull: ["$max", true] }] }
})

because according Ascending/Descending Sort a boolean value is always greater than any numeric value.

Don't mistake the Comparison Query Operators with Comparison Expression Operators. The Comparison Query Operators are used in find directly, the Comparison Expression Operators are used in Aggregation Pipelines and $expr documents.