Major - P3 Insert one object into a collection:
db.x.insert({a: {b: {str: "Health", num: 1358, c: {str: "parsing Chicken Handmade Concrete Pizza", "date" : ISODate("2019-03-26T16:54:52.891Z")}}}})
and run a non-matching query
db.x.aggregate([{$match: {"a.b.c.str": {$gte: "PNG"}}}, {$match: {$and: [{"a.b.str": {$not: {$lte: "Legacy"}}}]}}, {$project: {"a.b.num": 1}}])
you will get no result. The plan for this query is:
"slots" : "$$RESULT=s6 env: { s4 = \"PNG\", s5 = \"Legacy\" }", "stages" : "[2] project [s6 = makeBsonObj(MakeObjSpec([_id, a = MakeObj([b = MakeObj([num], Closed, RetNothing)], Closed, RetNothing)], Closed, RetNothing), s2, false)] [1] filter {(traverseF(s1, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"str\"), lambda(l104.0) { ((move(l104.0) >= s4) ?: false) }, false) }, false) }, false) }, false) && !(traverseF(s1, lambda(l105.0) { traverseF(getField(move(l105.0), \"b\"), lambda(l106.0) { traverseF(getField(move(l106.0), \"str\"), lambda(l107.0) { ((move(l107.0) <= s5) ?: false) }, false) }, false) }, false)))} [1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"08a693ed-8215-4b68-ab31-1d2da2b3739a\" true false "
using s4 and s5 to hold the constants used in the plan.
Now add a $project stage and another $match with a single $or inside:
db.x.explain().aggregate([{$match: {"a.b.c.str": {$gte: "PNG"}}}, {$match: {$and: [{"a.b.str": {$not: {$lte: "Legacy"}}}]}}, {$project: {"a.b.num": 1}}, {$match: {$or: [{"a.b.c.d.num": {$not: {$eq: NumberDecimal(0)}}}]}}])
The plan will add the "0" constant to the list of global slots as s8:
"slots" : "$$RESULT=s6 env: { s4 = \"PNG\", s5 = \"Legacy\", s8 = 0 }", "stages" : "[3] filter {!(traverseF(s7, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"d\"), lambda(l104.0) { traverseF(getField(move(l104.0), \"num\"), lambda(l105.0) { ((move(l105.0) == s8) ?: false) }, false) }, false) }, false) }, false) }, false))} \n[2] project [s7 = getField(s6, \"a\")] \n[2] project [s6 = makeBsonObj(MakeObjSpec([_id, a = MakeObj([b = MakeObj([num], Closed, RetNothing)], Closed, RetNothing)], Closed, RetNothing), s2, false)] \n[1] filter {(traverseF(s1, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"str\"), lambda(l104.0) { ((move(l104.0) >= s4) ?: false) }, false) }, false) }, false) }, false) && !(traverseF(s1, lambda(l105.0) { traverseF(getField(move(l105.0), \"b\"), lambda(l106.0) { traverseF(getField(move(l106.0), \"str\"), lambda(l107.0) { ((move(l107.0) <= s5) ?: false) }, false) }, false) }, false)))} \n[1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"08a693ed-8215-4b68-ab31-1d2da2b3739a\" true false "
When we add another expression to the $or, testing the same a.b.c.d.num field tested before
db.x.explain().aggregate([{$match: {"a.b.c.str": {$gte: "PNG"}}}, {$match: {$and: [{"a.b.str": {$not: {$lte: "Legacy"}}}]}}, {$project: {"a.b.num": 1}}, {$match: {$or: [{"a.b.c.d.num": {$not: {$eq: NumberDecimal(0)}}}, {"a.b.c.d.num": {$eq: NumberLong(0)}}]}}])
has the effect of reusing s4 for the operation, and replaces the "PNG" value with the new (reused) "0" value:
"slots" : "$$RESULT=s6 env: { s4 = 0, s5 = \"Legacy\", s8 = 0 }", "stages" : "[3] filter {(!(traverseF(s7, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"d\"), lambda(l104.0) { traverseF(getField(move(l104.0), \"num\"), lambda(l105.0) { ((move(l105.0) == s8) ?: false) }, false) }, false) }, false) }, false) }, false)) || traverseF(s7, lambda(l106.0) { traverseF(getField(move(l106.0), \"b\"), lambda(l107.0) { traverseF(getField(move(l107.0), \"c\"), lambda(l108.0) { traverseF(getField(move(l108.0), \"d\"), lambda(l109.0) { traverseF(getField(move(l109.0), \"num\"), lambda(l110.0) { ((move(l110.0) == s4) ?: false) }, false) }, false) }, false) }, false) }, false))} \n[2] project [s7 = getField(s6, \"a\")] [2] project [s6 = makeBsonObj(MakeObjSpec([_id, a = MakeObj([b = MakeObj([num], Closed, RetNothing)], Closed, RetNothing)], Closed, RetNothing), s2, false)] [1] filter {(traverseF(s1, lambda(l101.0) { traverseF(getField(move(l101.0), \"b\"), lambda(l102.0) { traverseF(getField(move(l102.0), \"c\"), lambda(l103.0) { traverseF(getField(move(l103.0), \"str\"), lambda(l104.0) { ((move(l104.0) >= s4) ?: false) }, false) }, false) }, false) }, false) && !(traverseF(s1, lambda(l105.0) { traverseF(getField(move(l105.0), \"b\"), lambda(l106.0) { traverseF(getField(move(l106.0), \"str\"), lambda(l107.0) { ((move(l107.0) <= s5) ?: false) }, false) }, false) }, false)))} [1] scan s2 s3 none none none none none none lowPriority [s1 = a] @\"08a693ed-8215-4b68-ab31-1d2da2b3739a\" true false "
Note that if the second test on the a.b.c.d.num field was against a different value, e.g. "1", it would be assigned to a new s9 global slot