Find records where at least one fulfills a condition

I'm looking to find some records from Mongo where they all fulfill some condition and that at least one of them fulfills a second, separate, condition.

Example:

Say records have counter & isHandled parameters (Number & Boolean respectfully).
I'd like to find all records where counter >= 100 and that at least one of the records that fulfill the first requirement also has isHandled set to false.

Say I have these records:

[
  {
    "_id": "1",
    "counter": 50,
    "isHandled": false
  },
  {
    "_id": "2",
    "counter": 50,
    "isHandled": true
  },
  {
    "_id": "3",
    "counter": 100,
    "isHandled": true
  },
  {
    "_id": "4",
    "counter": 100,
    "isHandled": true
  },
  {
    "_id": "5",
    "counter": 120,
    "isHandled": false
  },
  {
    "_id": "6",
    "counter": 100,
    "isHandled": true
  }
]

And also threshold = 100. My query should return the records with _id "3", "4", "5" & "6". However, if record "5" was not there, then no record should be returned. The reason is that since all of the records that satisfy the first condition (counter), none satisfy the second condition.

My first part might be { counter: { $gte: threshold } }.

Any idea how I can achieve this? Some sort of pipeline?

You can do the followings in an aggregation pipeline:

1. $match with counter : {$gte: threshold}
2. $setWindowFields to compute $rank for isHandled field. The trick here is if all records are true, they will all share the rank: 1. If some documents are false, the false documents will have rank: 1 and true documents will have rank: 2
3. $match all records by a $or condition on isHandled: false or rank: 2( that means they are true documents, but there exists some false documents to make it having rank 2)

db.collection.aggregate([
  {
    "$match": {
      counter: {
        $gte: 100
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        "isHandled": 1
      },
      "output": {
        "rank": {
          $denseRank: {}
        }
      }
    }
  },
  {
    $match: {
      $expr: {
        $or: [
          {
            $eq: [
              "$isHandled",
              false
            ]
          },
          {
            $eq: [
              "$rank",
              2
            ]
          }
        ]
      }
    }
  },
  {
    $unset: "rank"
  }
])

Mongo Playground

EDIT: thanks for @Noel 's comment. Changed to use $denseRank from $rank

You can try this one:

const threshold = 100;

db.collection.find({ 
  $and: [
    { counter: { $gte: threshold } }, 
    { 
      $or: [
        { isHandled: false }, 
        { isHandled: { $exists: false } }
      ]
    }
  ]
});