In this $lookup, there are two $match conditions. The first is static, while the second is dynamic.
Would mongodb’s query planner be able to optimize and run the static match condition once to find the superset of matching documents, cached it, and then from this superset run the second match condition for each document? And, bonus, is it able to use the index for the second conditon?
And if mongodb currently isn’t smart enough to do the above, how can I make request for this feature to be added?
If this is not something you are looking for, could you help me with a sample document from the collections along with the desired output for the aggregation performed. Also, specify the MongoDB version you are using.