Question 206064 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to add conditions inside $lte?

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You should use $project for compare the fields same document. Try this:

    db.collection.aggregate([
  {
    $project: {
      isCorrect: {
        "$cond": {
          "if": ["$max",null],
          "then": true,
          "else": {
            "$lte": ["$have","$max"]
          }
        }
      },
      doc: "$$ROOT"// for get the whole documents
      
    }
  },
  {
    "$match": {
      isCorrect: true
    }
  }
])

    Link mongo playground at here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search