0

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.

Fernando Lemos
  • 297
  • 3
  • 9
  • The shell actually wraps the aggregate command to return a "cursor". Most general API's actually need this to be specified explicitly as an option and otherwise attempt to return a BSON response containing an array of all results. So you need to set the options to return the cursor. – Neil Lunn Aug 30 '17 at 09:53
  • Thanks @Neil Lunn. It worked! – Fernando Lemos Aug 30 '17 at 10:35

0 Answers0