Query based on date range behaving differently on Mongo CLI and Casbah

29 Views Asked by At

The following query works on Mongo CLI, but not on Casbah API.

The query:

db.collection.aggregate([
    {$unwind:'$views'},
    {$project:{'views': 1}},
    {$match:{'views.date':{$gte: ISODate('2017-06-01T00:00:00.000Z'), $lt: ISODate('2017-06-02T00:00:00.000Z')}}}
])

The query works on Mongo CLI and returns pertinent results in few seconds. Here is the execution plan provided by Mongo CLI:

{
    "stages": [{
        "$cursor": {
            "query": {},
            "fields": {
                "views": 1,
                "_id": 1
            },
            "queryPlanner": {
                "plannerVersion": 1,
                "namespace": "database.collection",
                "indexFilterSet": false,
                "parsedQuery": {
                    "$and": []
                },
                "winningPlan": {
                    "stage": "COLLSCAN",
                    "filter": {
                        "$and": []
                    },
                    "direction": "forward"
                },
                "rejectedPlans": []
            }
        }
    },
    {
        "$unwind": "$views"
    },
    {
        "$project": {
            "views": true
        }
    },
    {
        "$match": {
            "views.date": {
                "$gte": ISODate("2017-06-01T00:00:00Z"),
                "$lt": ISODate("2017-06-02T00:00:00Z")
            }
        }
    }],
    "ok": 1
}

When I execute the query using Cashbah, I get the following error after 4 minutes:

Command failed with error 16389: 'exception: aggregation result exceeds maximum document size (16MB)' on server ip:port.

Here is the scala code using Casbah 3.1.1:

val client = MongoClient(host, port)
val database = client(databaseName)
val coll = database(collectionName)

val explode = BasicDBObject.parse("{$unwind:'$views'}")
val projection = BasicDBObject.parse("{$project:{'views': 1}}")
val query = BasicDBObject.parse("{$match:{'views.date':{$gte: ISODate('2017-06-01T00:00:00.000Z'), $lt: ISODate('2017-06-02T00:00:00.000Z')}}}")
coll.aggregate(Seq(explode, projection, query)).results

Here is the execution plan Cashbah provides:

{
    [{
        "$cursor": {
            "query": {},
            "fields": {
                "views": 1,
                "_id": 1
            },
            "queryPlanner": {
                "plannerVersion": 1,
                "namespace": "kimono.real_estate_ads",
                "indexFilterSet": false,
                "parsedQuery": {
                    "$and": []
                },
                "winningPlan": {
                    "stage": "COLLSCAN",
                    "filter": {
                        "$and": []
                    },
                    "direction": "forward"
                },
                "rejectedPlans": []
            }
        }
    },
    {
        "$unwind": "$views"
    },
    {
        "$project": {
            "views": true
        }
    },
    {
        "$match": {
            "views.date": {
                "$gte": {
                    "$date": "2017-06-01T12:00:00.000Z"
                },
                "$lt": {
                    "$date": "2017-06-02T00:00:00.000Z"
                }
            }
        }
    }],
    "ok": 1
}

The only difference between both execution plans is how the date is represented:

  • ISODate("2017-06-01T00:00:00Z") when using Mongo CLI
  • "$date": "2017-06-02T00:00:00.000Z" when using Casbah

What I've tried :

  • Change the order of the elements in the pipeline: the presented order is the one that provides the best execution time on Mongo CLI; the other possibilites return the same error on Casbah
  • Replace ISODate(...) by new ISODate(...), {$date:'...'}, {'$date':'...'}, {"$date":"..."}: makes no difference

There are other arrays on the same collection having similar structure over which the query worked.

Any help is appreciated.

0

There are 0 best solutions below