MongoDB Aggregation Query Optimization : match -> unwind -> match vs unwind->match

Question

Input Data

{
    "_id" : ObjectId("5dc7ac6e720a2772c7b76671"),
    "idList" : [ 
        {
            "queueUpdateTimeStamp" : "2019-12-12T07:16:47.577Z",
            "displayId" : "H14",
            "currentQueue" : "10",
            "isRejected" : true,
            "isDispacthed" : true
        },
        {
            "queueUpdateTimeStamp" : "2019-12-12T07:16:47.577Z",
            "displayId" : "H14",
            "currentQueue" : "10",
            "isRejected" : true,
            "isDispacthed" : false
        }
    ],
    "poDetailsId" : ObjectId("5dc7ac15720a2772c7b7666f"),
    "processtype" : 1
}

Output Data

{
    "_id" : ObjectId("5dc7ac6e720a2772c7b76671"),
    "idList":
     {
            "queueUpdateTimeStamp" : "2019-12-12T07:16:47.577Z",
            "displayId" : "H14",
            "currentQueue" : "10",
            "isRejected" : true,
            "isDispacthed" : true
    },
    "poDetailsId" : ObjectId("5dc7ac15720a2772c7b7666f"),
    "processtype" : 1
}

Query 1 ( unwind then match)

     aggregate([
     {
         $unwind: { path: "$idList" }
     },
     {
         $match: { 'idList.isDispacthed': isDispatched }
     }
     ])

Query 2 ( match then unwind then match)

     aggregate([
     {
         $match: { 'idList.isDispacthed': isDispatched }
     },
     {
         $unwind: { path: "$idList" }
     },
     {
         $match: { 'idList.isDispacthed': isDispatched }
     }
     ])

My Question / My Concern

(suppose i have large number of documents(50k +) in this collection and assuming i have other lookups and projections after this query in same pipeline)

match -> unwind -> match VS unwind ->match

1. is there any performance difference between these two queries ?
2. is there any other ( better ) way to write this query?

Answer 1

It all depends on the MongoDB query planner optimizer:

Aggregation pipeline operations have an optimization phase which attempts to reshape the pipeline for improved performance.

To see how the optimizer transforms a particular aggregation pipeline, include the explain option in the db.collection.aggregate() method.

https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/

Create index for poDetailsId and run this query:

db.getCollection('collection').explain().aggregate([
     {
         $unwind: "$idList"
     },
      {
         $match: { 
           'idList.isDispacthed': true, 
           "poDetailsId" : ObjectId("5dc7ac15720a2772c7b7666f") 
         }
     }  
])

---

{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "poDetailsId" : {
                        "$eq" : ObjectId("5dc7ac15720a2772c7b7666f")
                    }
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "test.collection",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "poDetailsId" : {
                            "$eq" : ObjectId("5dc7ac15720a2772c7b7666f")
                        }
                    },
                    "queryHash" : "2CF7E390",
                    "planCacheKey" : "A8739F51",
                    "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "poDetailsId" : 1.0
                            },
                            "indexName" : "poDetailsId_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "poDetailsId" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "poDetailsId" : [ 
                                    "[ObjectId('5dc7ac15720a2772c7b7666f'), ObjectId('5dc7ac15720a2772c7b7666f')]"
                                ]
                            }
                        }
                    },
                    "rejectedPlans" : []
                }
            }
        }, 
        {
            "$unwind" : {
                "path" : "$idList"
            }
        }, 
        {
            "$match" : {
                "idList.isDispacthed" : {
                    "$eq" : true
                }
            }
        }
    ],
    "ok" : 1.0
}

As you see MongoDB will change this aggregation to:

db.getCollection('collection').aggregate([
     {
         $match: { "poDetailsId" : ObjectId("5dc7ac15720a2772c7b7666f") }
     }
     {
         $unwind: "$idList"
     },
     {
         $match: { 'idList.isDispacthed': true }
     }  
])

Logically, $match -> $unwind -> $match is better since you filter (by index) a subset of records instead of full scan (working with 100 matched documents ≠ all documents).

If your aggregation operation requires only a subset of the data in a collection, use the $match, $limit, and $skip stages to restrict the documents that enter at the beginning of the pipeline. When placed at the beginning of a pipeline, $match operations use suitable indexes to scan only the matching documents in a collection.

https://docs.mongodb.com/manual/core/aggregation-pipeline/#early-filtering

Once you manipulate your documents, MongoDB cannot apply indexes.