CODEWITHSUNDEEP
mongodbaggregation-framework
Pavel Bely
Pavel Bely

Reputation: 2405

MongoDB aggregation explain provides data only about first stages

I'm running the following aggregation query on a test database

db.restaurants.explain().aggregate([
  {$match: {"address.zipcode": {$in: ["10314", "11208", "11219"]}}},
  {$match: {"grades": {$elemMatch: {score: {$gte: 1}}}}},
  {$group: {_id: "$borough", count: {$sum: 1} }}, 
  {$sort: {count: -1} }
]);

And as per MongoDB documentation it should return cursor that I can iterate and see data about all pipeline stages:

The operation returns a cursor with the document that contains detailed information regarding the processing of the aggregation pipeline.

However the aggregation command returns explain info only about first two match stages:

{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "$and" : [ 
                        {
                            "address.zipcode" : {
                                "$in" : [ 
                                    "10314", 
                                    "11208", 
                                    "11219"
                                ]
                            }
                        }, 
                        {
                            "grades" : {
                                "$elemMatch" : {
                                    "score" : {
                                        "$gte" : 1.0
                                    }
                                }
                            }
                        }
                    ]
                },
                "fields" : {
                    "borough" : 1,
                    "_id" : 0
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "test.restaurants",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "$and" : [ 
                            {
                                "grades" : {
                                    "$elemMatch" : {
                                        "score" : {
                                            "$gte" : 1.0
                                        }
                                    }
                                }
                            }, 
                            {
                                "address.zipcode" : {
                                    "$in" : [ 
                                        "10314", 
                                        "11208", 
                                        "11219"
                                    ]
                                }
                            }
                        ]
                    },
                    "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "grades" : {
                                        "$elemMatch" : {
                                            "score" : {
                                                "$gte" : 1.0
                                            }
                                        }
                                    }
                                }, 
                                {
                                    "address.zipcode" : {
                                        "$in" : [ 
                                            "10314", 
                                            "11208", 
                                            "11219"
                                        ]
                                    }
                                }
                            ]
                        },
                        "direction" : "forward"
                    },
                    "rejectedPlans" : []
                }
            }
        }, 
        {
            "$group" : {
                "_id" : "$borough",
                "count" : {
                    "$sum" : {
                        "$const" : 1.0
                    }
                }
            }
        }, 
        {
            "$sort" : {
                "sortKey" : {
                    "count" : -1
                }
            }
        }
    ],
    "ok" : 1.0
}

And the object returned does not seem like cursor at all.
If I save the aggregation result to a variable and then try to iterate through it using cursor methods (hasNext(), next(), etc) I get the following:

TypeError: result.next is not a function : @(shell):1:1

How can I see info on all pipeline steps?
Thanks

Upvotes: 2

Views: 3409

Answers (1)

felix
felix

Reputation: 9285

1. Explain info

Explain() returns the winning plan of a query, ie how the database fetch the document before processing them in the pipeline.

Here, because adress.zipcode and grades aren't indexed, the db performs a COLLSCAN, ie iterate over all documents in db and see if they match

After that, you group the document and sort the results. Thoses operations are done "in memory", on the previously fetched documents. The fields aren't indexed, so no special plan can be used here

more info here : explain results

2. Explain() on aggregation query does not return a cursor

For some reason, explain() on aggregation query does not return a cursor, but a BSON object directly (unlike explain() on find() query )

It might be a bug, but there's nothing about this in the doc.

Anyway, you can do : 

var explain =  db.restaurants.explain().aggregate([
  {$match: {"address.zipcode": {$in: ["10314", "11208", "11219"]}}},
  {$match: {"grades": {$elemMatch: {score: {$gte: 1}}}}},
  {$group: {_id: "$borough", count: {$sum: 1} }}, 
  {$sort: {count: -1} }
]);
printjson(explain)

Upvotes: 4

Related Questions