Geek Answers Handbook

MongoDB (3.0) Aggregation: multiple matches versus one match with multiple items

I am working on a project that requires me to create a dynamic MongoDB query on the fly based on a large number of matches (possibly a potential of 100). Besides creating the right indexes, I was wondering if it was important that I build the matches in the pipeline. Based on the following example, does one of these examples work differently or is it better than the other?

I assume example 2 will crash the result set, but there will be more calls? Perhaps this is what example 1 does behind the scenes?

Thanks in advance for your help!

Example 1

db.Test.aggregate( [ { $match: { item1: 'foo1', item2: 'foo2', item3: 'foo3' } } ])

vs

Example 2

 db.Test.aggregate( [ { $match: { item1: 'foo1' } }, { $match: { item2: 'foo2' } }, { $match: { item3: 'foo3' } } ])

I doubt this is important for this question, but if appropriate, I will use the C # driver for my implementation.

+5
source share
2 answers

I found the following information in the MongoDB Documentation :

$match + $match Coalescence

When a $match immediately follows another $match , the two stages can be combined into a single match $, combining the conditions with $and . For example, the pipeline contains the following sequence:

 { $match: { year: 2014 } }, { $match: { status: "A" } }

Then the second stage of $ match can merge into the first stage of $ match and lead to one stage of $ match:

 { $match: { $and: [ { "year" : 2014 }, { "status" : "A" } ] } }

From this we can say that the use of several $match in its raw form is strictly equivalent to the use of one $match with several fields.

However, I'm not sure why the $and operator adds the optimization mechanism here. This is not necessary according to this answer , so I think it can be ignored. Can anyone confirm?

+1
source

I was wondering the same thing today and came across Romain's answer. Although I wanted to see this for myself, that can easily be done using the explanation for both units;

 db.verpakking.explain().aggregate([ { "$match": {type: "VERPAKT"} }, { "$match": {ras: "CherryStar"} }, ]);

The result is the following:

 { "waitedMS" : NumberLong(0), "stages" : [ { "$cursor" : { "query" : { "$and" : [ { "type" : "VERPAKT" }, { "ras" : "CherryStar" } ] }, "queryPlanner" : { "plannerVersion" : NumberInt(1), "namespace" : "denberk.verpakking", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "ras" : { "$eq" : "CherryStar" } }, { "type" : { "$eq" : "VERPAKT" } } ] }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "$and" : [ { "ras" : { "$eq" : "CherryStar" } }, { "type" : { "$eq" : "VERPAKT" } } ] }, "direction" : "forward" }, "rejectedPlans" : [ ] } } } ], "ok" : NumberInt(1) }

Until

 db.verpakking.explain().aggregate([ { "$match": {type: "VERPAKT", ras: "CherryStar"} }, ]);

Output Results:

 { "waitedMS" : NumberLong(0), "stages" : [ { "$cursor" : { "query" : { "type" : "VERPAKT", "ras" : "CherryStar" }, "queryPlanner" : { "plannerVersion" : NumberInt(1), "namespace" : "denberk.verpakking", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "ras" : { "$eq" : "CherryStar" } }, { "type" : { "$eq" : "VERPAKT" } } ] }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "$and" : [ { "ras" : { "$eq" : "CherryStar" } }, { "type" : { "$eq" : "VERPAKT" } } ] }, "direction" : "forward" }, "rejectedPlans" : [ ] } } } ], "ok" : NumberInt(1) }

As you can see, this is exactly the same, except for the "request" part (which is normal, since our request was ). This proves that if you use two separate consecutive $ match-pipelines or one combined $ match-pipeline, the parsed request will be exactly the same.

0
source

More articles:


All Articles