Mongo $expr return when delta of 2 fields exceeds 20%-CodePudding

This will return docs where field1 is greater that field2

collection.find( { $expr: { $gt: [ "$field1" , "$field2" ] } } )

I want to get documents where difference between field1 and field2 is greater or less than 20. Can that be done?

something like this.

{
  match: {
    $expr: {
      {
        $expr: {
          $gt: [
            $subtract: ["$field1", "$field2"], 20
          ]
        }
      }
    }
  }
}

not looking for an aggregation solution

CodePudding user response：

Yes, you can just use a similar query to what you're already doing, you just have to check both ways field1 - field2 > 20 or field2 - field1 > 20.

You can easily achieve this using $abs (absolute(field1 - field2) > 20), like so:

db.collection.find({
  $expr: {
    $gt: [
      {
        $abs: {
          $subtract: [
            "$field1",
            "$field2"
          ]
        }
      },
      20
    ]
  }
})

Mongo Playground