MongoDB - Combination of Logical query operators not working as expected

I am trying to filter data based on multiple conditions using Collection.find({...}) method but the output is not as expected, where userid = 6497cda5517300ca6a7e9767.

{
    $and: [
      {
        $or: [
          { from: userid },
          { to:   userid },
        ],
      },
      {
        $not: {
          $and: [
            { to: userid },
            { status: "YOUR_TURN" },
          ],
        },
      },
    ],
}

Output :

[
  {
    from: new ObjectId("6497cc25517300ca6a7e973f"),
    to: new ObjectId("6497cda5517300ca6a7e9767"),
    status: 'COMPLETED',
  },
  {
    from: new ObjectId("6497cc25517300ca6a7e973f"),
    to: new ObjectId("6497cda5517300ca6a7e9767"),
    status: 'YOUR_TURN',
  },
  {
    from: new ObjectId("6497cda5517300ca6a7e9767"),
    to: new ObjectId("6497cc25517300ca6a7e973f"),
    status: 'WAITING',
  }
]

The 2nd document does not satisfy the condition but still include in the result. What is the correct query to exclude 2nd doc?

Doubt that your current query is valid. You will get the error:

> query failed: (BadValue) unknown top level operator: $not. If you are trying to negate an entire expression, use $nor.

Either:

1. Replace $not with $nor and provide the conditions in array

db.collection.find({
  $and: [
    {
      $or: [
        {
          from: ObjectId("6497cda5517300ca6a7e9767")
        },
        {
          to: ObjectId("6497cda5517300ca6a7e9767")
        }
      ]
    },
    {
      $nor: [
        {
          $and: [
            {
              to: ObjectId("6497cda5517300ca6a7e9767")
            },
            {
              status: "YOUR_TURN"
            }
          ]
        }
      ]
    }
  ]
})

Demo Approach 1 @ Mongo Playground

Or you need the $expr operator and change the query as below:

db.collection.find({
  $expr: {
    $and: [
      {
        $or: [
          {
            $eq: [
              "$from",
              ObjectId("6497cda5517300ca6a7e9767")
            ]
          },
          {
            $eq: [
              "$to",
              ObjectId("6497cda5517300ca6a7e9767")
            ]
          }
        ]
      },
      {
        $not: {
          $and: [
            {
              $eq: [
                "$to",
                ObjectId("6497cda5517300ca6a7e9767")
              ]
            },
            {
              $eq: [
                "$status",
                "YOUR_TURN"
              ]
            }
          ]
        }
      }
    ]
  }
})

Demo Approach 2 @ Mongo Playground