[DOCS-14252] Investigate changes in SERVER-39943: Create match expressions for aggregation $gt/$gte/$lt/$lte and add to $expr rewrite Created: 25/Feb/21  Updated: 02/Jan/24  Resolved: 15/Mar/21

Status: Closed
Project: Documentation
Component/s: manual, Server
Affects Version/s: None
Fix Version/s: 4.9.0

Type: Task Priority: Major - P3
Reporter: Backlog - Core Eng Program Management Team Assignee: Jason Price
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
documents SERVER-39943 Create match expressions for aggregat... Closed
Participants:
Days since reply: 2 years, 47 weeks, 2 days ago
Epic Link: DOCSP-9747
Story Points: 2

 Description   

Description

Downstream Change Summary

Marking this for documentation changes since I noticed we document that we only use indexes for equality predicates in the 'let' parameter documentation for $lookup:
https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#lookup-join-let

After this ticket, any comparison inside $expr is eligible for index use, if the right index exists: $eq, $lt, $lte, $gt, $gte

Description of Linked Ticket

When aggregation operators such as $gt/$gte/$lt/$lte are used within $expr, we will not rewrite to match expression. This is because they would not be eligible to use indexes due to type bracketing differences between match and aggregation. We should consider adding regardless, as the rewrite would make these expressions eligible for aggregation match optimization, such as repositioning a comparison expression in the pipeline.

The following is an example of where this would be useful. Ideally we would rewrite the {$expr": {"$gt" : [ "$x",

{"$const": 5}

]}} and move before the $lookup stage as an optimization.

db.foo.explain().aggregate({$lookup:{as:"a",from:"b",localField:"x",foreignField:"y"}},{$unwind:"$a"},{$match:{"a.z":10,$expr:{$gt:["$x",5]}}})
        "stages" : [
                {
                        "$cursor" : {
                                "query" : {
                                },
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "tpcds10.foo",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                        },
                                        "winningPlan" : {
                                                "stage" : "EOF"
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        }
                },
                {
                        "$lookup" : {
                                "from" : "b",
                                "as" : "a",
                                "localField" : "x",
                                "foreignField" : "y"
                        }
                },
                {
                        "$match" : {
                                "$and" : [
                                        {
                                                "a.z" : {
                                                        "$eq" : 10
                                                }
                                        },
                                        {
                                                "$expr" : {
                                                        "$gt" : [
                                                                "$x",
                                                                {
                                                                        "$const" : 5
                                                                }
                                                        ]
                                                }
                                        }
                                ]
                        }
                }
        ],
        "ok" : 1
}

Scope of changes

Impact to Other Docs

MVP (Work and Date)

Resources (Scope or Design Docs, Invision, etc.)



 Comments   
Comment by Githook User [ 15/Mar/21 ]

Author:

{'name': 'Jason Price', 'email': 'jason.price@mongodb.com', 'username': 'jason-price-mongodb'}

Message: DOCS-14252 create match expressions for aggregation
Branch: v5.0
https://github.com/mongodb/docs/commit/3abfc47c8cd3c9b25f31e61628e8c7cd25c18b63

Generated at Thu Feb 08 08:09:54 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.