SQL equivalent queries in MongoDB-CodePudding

I'm converting my SQL queries into MongoDB. I have the following SQL statement which checks an input and perform the query accordingly.

(score_min = -1 OR Scores BETWEEN score_min AND score_max)

Scores is a field name, which I want to filter
Still couldn't figure out the best way to perform this query in a MongoDB aggregate pipeline. Any suggestions please?

CodePudding user response：

You can achieve the behaviour with a $expr in a simple find. Not a must to use aggregation pipeline.

db.collection.find({
  $expr: {
    $or: [
      {
        // $eq: [<score_min>, -1]
        $eq: [
          <score_min>,
          -1
        ]
      },
      {
        $and: [
          {
            // $gte: ["$Scores", <score_min>]
            $gte: [
              "$Scores",
              <score_min>
            ]
          },
          {
            $lte: [
              // $gte: ["$Scores", <score_max>]
              "$Scores",
              <score_max>
            ]
          }
        ]
      }
    ]
  }
})

Here is the Mongo playground for your reference.