Query MongoDB with $and and Multiple $or

问题:

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"
                }
            ]
        }
    ]
}

回答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 (implicit $and)

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

whereas this would work (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