I want records which if type have 0 or 2 than status should not equal to 0 and if type = 3 then ignore status.

I tried this way but not working.

db.activities.find({
    $and: [
        {
            $or: [
                { type: { $in: [0, 2] } },
                { status: { $ne: 0 } },
            ],
        },
        {
            $or: [{ type: { $eq: 3 } }],
        },
    ]
})
    .projection({})
    .sort({ _id: -1 })
    .limit(100)

CodePudding user response：

You can try this query where:

Get all documents where type is 3 (not checks status) or the values where type is 0 or 2 and status is not 0 (as you have in your query)

The difference between this query and your one is you are using $and, then the type has to be 3 AND 0 or 2. A number can't be two values at the same time.

db.collection.find({
  $or: [
    {
      $and: [
        {
          type: {
            $in: [
              0,
              2
            ]
          }
        },
        {
          status: {
            $ne: 0
          }
        }
      ]
    },
    {
      type: 3
    }
  ]
})

Example here