CODEWITHSUNDEEP
mongodbmongodb-query
Damian Helme
Damian Helme

Reputation: 1121

MongoDB: two $or's combined with an implicit AND - expected behaviour?

In the MongoDB documentation is says that 

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

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

https://docs.mongodb.com/manual/reference/operator/query/and/

What should you expect if you do use implicit AND for two OR's? Is the behaviour well defined?

From this experiment, it looks like it ignores the first OR predicate.. 

> db.mytest.insertMany([
{w: 0,  x: 0, y: 0, z: 0 },
{w: 0,  x: 0, y: 1, z: 0 },
{w: 0,  x: 0, y: 1, z: 1 },
{w: 0,  x: 1, y: 0, z: 0 },
{w: 0,  x: 1, y: 0, z: 1 },
{w: 0,  x: 1, y: 1, z: 0 },
{w: 0,  x: 1, y: 1, z: 1 },
{w: 1,  x: 0, y: 0, z: 0 },
{w: 1,  x: 0, y: 1, z: 0 },
{w: 1,  x: 0, y: 1, z: 1 },
{w: 1,  x: 1, y: 0, z: 0 },
{w: 1,  x: 1, y: 0, z: 1 },
{w: 1,  x: 1, y: 1, z: 0 },
{w: 1,  x: 1, y: 1, z: 1 },
])
...
>db.mytest.find({$or: [ {w: 1}, { x: 1} ],$or: [ {y: 1}, { z: 1} ]})
{ "_id" : ObjectId("59ce696828f7ac6a3372e64d"), "w" : 0, "x" : 0, "y" : 1, "z" : 0 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e64e"), "w" : 0, "x" : 0, "y" : 1, "z" : 1 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e650"), "w" : 0, "x" : 1, "y" : 0, "z" : 1 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e651"), "w" : 0, "x" : 1, "y" : 1, "z" : 0 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e652"), "w" : 0, "x" : 1, "y" : 1, "z" : 1 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e654"), "w" : 1, "x" : 0, "y" : 1, "z" : 0 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e655"), "w" : 1, "x" : 0, "y" : 1, "z" : 1 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e657"), "w" : 1, "x" : 1, "y" : 0, "z" : 1 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e658"), "w" : 1, "x" : 1, "y" : 1, "z" : 0 }
{ "_id" : ObjectId("59ce696828f7ac6a3372e659"), "w" : 1, "x" : 1, "y" : 1, "z" : 1 }
>

I'm asking the question because the behaviour seems to me to be inconsistent. I mistakingly used an implicit AND with OR's in a couple of places in my code. One of them appeared to give the same answer as you would expect from using and explicit AND. It was a complex query, so I may have made a mistake either in the query or in the testing. It would be good to know what's going on here ... there's possibly another bug that I'm still to weed out.

Upvotes: 2

Views: 148

Answers (1)

4J41
4J41

Reputation: 5095

MongoDB treats 

{$or: [ {w: 1}, { x: 1} ],$or: [ {y: 1}, { z: 1} ]}

as 

{$or: [ {y: 1}, { z: 1} ]}

When 2 keys are the same in a MongoDB Query, the latest one replaces the earlier one.

As explained in the docs, you will need a explicit $and here.

Upvotes: 3

Related Questions