MongoDB - Combination of Logical query operators not working as expected

Question

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?

Answer 1

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