Does the sequence of the criteria in MQL matter to the result?

I had an experiment on MQL queries to try to find out if the sequence of the criteria matters to the result.
This is the first try with the sample_training.companies collection.

db.companies.find(
	{"$or": [
		{"category_code": "social"}, 
		{"category_code": "web"}
	]}, 
	{"$or": [
		{"founded_year": 2004}, 
		{"founded_month": 10}
	]}
).count()

The result is 1981.

Here is the second try:

db.companies.find(
	{"$or": [
		{"founded_year": 2004}, 
		{"founded_month": 10}
	]},
	{"$or": [
		{"category_code": "social"}, 
		{"category_code": "web"}
	]}
).count()

The result is 668.

As the M001 course said, the form {criteria-1}, {criteria-2} will be taken as a $and operation as default (The $and operator is applied implicitly). I wondered why I got two different results. Then I tried to add $and operator explicitly and see what would happen.

The third try with adding $and operator explicitly.

db.companies.find(
	{"$and": [
		{"$or": [{"founded_year": 2004}, {"founded_month": 10}]}, 
		{"$or": [{"category_code": "social"}, {"category_code": "web"}]}
	]}
).count()

The result is 149.

And then I tried the fourth time with criteria swapped.

db.companies.find(
	{"$and": [
		{"$or": [{"category_code": "social"}, {"category_code": "web"}]},
		{"$or": [{"founded_year": 2004}, {"founded_month": 10}]}
	]}
).count()

The result is the same as the third try, 149.

This makes me a little confused. The experiment showed me that the MQL interpreter might treat the MQL statement differently regarding the explicit or implicit use of the $and operator.

In

your query is only

because the last brace above terminate the query object. The rest is taken as a projection.

This is the same issue with

I modified my MQL, and enclosed the two $or expressions with one pair of curly brackets so that the projection part was removed from the parameter list of find().

db.companies.find(
{
"$or": [{"category_code": "social"}, {"category_code": "web"}],
"$or": [{"founded_year": 2004}, {"founded_month": 10}]
}
).count()

The result was not as I expected. Then I went to the manual and found:


It seems like the two $or expressions were merged as the result of optimization.
Now I feel more confident with MQL.
Thanks a lot!

1 Like

The fact that you lose one of the $or and end up with the last one only, and that you are forced to use the explicit $and is more about JSON than MongoDB.

Most driver only keeps the last occurrence of a field. The $or in MQL is simply a field in a JSON document (the query).

You could not do the following without explicit $and.

{ qty : { $gt : 10 } , qty : { $lt : 50 } }

because the query ends up being only:

{ qty : { $lt : 50 } }

This being said implicit and can be used with the above query when written as:

{ qty : { $gt : 10 , $lt : 50 } }

because the smart developers of MongoDB wanted to simplify our life.

There is 2 ways to find out the effective query with mongosh.

First using simple JSON such as:

query = { qty : { $gt : 10 } , qty : { $lt : 50 } }
/* the output will be */
{ qty : { $lt : 50 } }

Second using the query planner of the explain plan:

query = { a : 1 , b : 2 }
db.c.find(query).explain().queryPlanner.parsedQuery
/* the output is the transformation of implicit to explicit and will be */
{ '$and' : [ { a: { '$eq': 1 } }, { b: { '$eq': 2 } } ]
2 Likes

Thank you so much for your detailed explanation and the practical way of MQL analysis.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.