Deterministic OR Operator

[ruby]

// pretend this is a collection.
const documents = [{bar: true}, {foo: true}];

const query = {$or: [{foo: true}, {bar: true}]};
documents.findOne(query) // which document will this return?

I need to create a query like if foo, else bar. Only return bar if foo doesn’t exist.
So my question is; is the or operator deterministic so that the order in the array determines which will be output first? If not, what would the best way to have a query to do such.

[Prasad_Saya]

Hello @ruby, welcome to the MongoDB Community forum!

@ruby

documents.findOne(query) // which document will this return?

The query returns both the documents.

@ruby

I need to create a query like if foo, else bar. Only return bar if foo doesn’t exist.

This is not clear. You mean:

if the field foo exists then return the value of foo 
else return the value of bar

[steevej (Steeve Juneau)]

Please be respectful.

@ruby

Your reply is unnecessary.

I disagree with the above, since your question

@ruby

// which document will this return?

has been clearly and unquestionably answered with

@Prasad_Saya

The query returns both the documents.

Since you did not clarify the following

@Prasad_Saya

if the field foo exists then return the value of foo
else return the value of bar

I will assume that this is what you want and try to propose something.

{ "$or" :
  [
    { "foo" : true } ,
    { "$and" : [ { "foo" : { "$exists" : false } } , { "bar" : true }  ] }
  ]
}

Of course, this is untested.

[Stennie_X (Stennie)]

@ruby

I need to create a query like if foo, else bar. Only return bar if foo doesn’t exist.
So my question is; is the or operator deterministic so that the order in the array determines which will be output first? If not, what would the best way to have a query to do such.

Welcome to the MongoDB Community @ruby!

The behaviour of findOne() is described in the MongoDB server documentation:

If multiple documents satisfy the query, this method returns the first document according to the natural order which reflects the order of documents on the disk. In capped collections, natural order is the same as insertion order. If no document satisfies the query, the method returns null.

Natural sort order is not deterministic (see: What is the default sort order when none is specified?).

However, if you want more deterministic results you can use a find() query with an explicit sort() which will follow the rules of Comparison/Sort Order.

In particular, you can use the fact that null values will be sorted before boolean values to create a sort() which returns a document with foo first, if present.

Example data:

db.mydata.insert([
    { "_id": "foo", "foo" : true },
    { "_id": "bar", "bar" : true },
    { "_id": "foobar", "foo" : true, "bar" : true }
])

// Find a document where `foo` or `bar` is true, but prefer `foo`
> db.mydata.find({$or: [{foo: true}, {bar: true}]}).sort({foo:-1}).limit(1)
{ "_id" : "foo", "foo" : true }

This query could also return { "_id": "foobar", "foo" : true, "bar" : true }, since multiple documents matching foo:true have equal value in a sort on foo. The order of result documents may not change frequently, but you should not rely on the same document being returned.

If you want a more stable order you could add additional fields to the sort criteria. For example, adding _id to the sort (since _id is guaranteed to be present and unique):

// Find a document where `foo` or `bar` is true, but prefer `foo` ordered by _id (descending)
> db.mydata.find({$or: [{foo: true}, {bar: true}]}).sort({foo:-1, _id:-1}).limit(1)
{ "_id" : "foobar", "foo" : true, "bar" : true }

If this is going to be a common query, you should also Create an Index to Sort Query Results.

Regards,
Stennie