Query MongoDB with $and and Multiple $or

Question

As stated in the documentation, this is not possible.

---

AND Queries With Multiple Expressions Specifying the Same Operator

Consider the following example:

db.inventory.find( {
    $and : [
        { $or : [ { price : 0.99 }, { price : 1.99 } ] },
        { $or : [ { sale : true }, { qty : { $lt : 20 } } ] }
    ]
} )

This query will return all select all documents where:

the price field value equals 0.99 or 1.99, and the sale field value is equal to true or the qty field value is less than 20.

This query cannot be constructed using an implicit AND operation, because it uses the $or operator more than once.

---

What is a workaround to query something like this? This query returns no results on MongoDB 3.2. I have tested the $or blocks separately and they are working fine, but not when they are wrapped in $and block. I assumed I didn't read the documentation incorrectly that this is not supposed to work. The only alternative I have is to push the data to ElasticSearch and query it there instead, but that's also just a workaround.

{
    "$and": [
        {
            "$or": [
                {
                    "title": {
                        "$regex": "^.*html .*$",
                        "$options": "i"
                    }
                },
                {
                    "keywords": {
                        "$regex": "^.*html .*$",
                        "$options": "i"
                    }
                }
            ]
        },
        {
            "$or": [
                {
                    "public": true
                },
                {
                    "domain": "cozybid"
                }
            ]
        }
    ]
}

Amoakuh

Answer 1

the documentation doesn't say that this is impossible. It only says

This query cannot be constructed using an implicit AND operation, because it uses the $or operator more than once.

this means that this will work :

db.inventory.find( {
    $and : [
        { $or : [ { price : 0.99 }, { price : 1.99 } ] },
        { $or : [ { sale : true }, { qty : { $lt : 20 } } ] }
    ]
} )

but this won't, because it's an implicit $and with two $or

db.inventory.find({
        { $or : [ { price : 0.99 }, { price : 1.99 } ] },
        { $or : [ { sale : true }, { qty : { $lt : 20 } } ] }
})

try it online: mongoplayground.net/p/gL_0gKzGA-u

Here is a working case with an implicit $and:

db.inventory.find({ price: { $ne: 1.99, $exists: true } })

I guess the problem you're facing is that there is no document matching your request in your collection

Sizar

I actually realized after I posted the question that the data has been rolled back on the server by my team. You are absolutely correct. It's the implicit AND that's not supposed to work. – Amoakuh

what if or replaces $and and $and replaced by $or – Concession