CODEWITHSUNDEEP
javamongodbmongodb-queryaggregation-framework
vinay
vinay

Reputation: 960

MongoDB date range query throws syntax errors when used in aggregation

I am using Java Driver for MongoDB to query database. I am getting syntax errors when trying to execute range query on a date field in aggregation. It works fine if I use it as part of find query only. Aggregation is the problem here. I used the following Java query code :

new BasicDBObject("requestDate", BasicDBObjectBuilder.start("$gte", fromDate).add("$lte", toDate).get());

requestDate is the field I want to query. I tried debugging the code and ran the generated query using command line and I still get syntax errors. Not sure whats wrong here.

Mongo Query generated by the code:

 { "requestDate" : { "$gte" : { "$date" : "2015-03-01T05:00:00.000Z"} , "$lte" : { "$date" : "2015-03-09T04:00:00.000Z"}}}

EDIT. Adding relevant Code:

BasicDBObject match = null;
if (organizationId != null) {
    match = new BasicDBObject("$match", new BasicDBObject("organizationId", organizationId));
}
if (optionalParams != null) {
    Date fromDate = (Date) optionalParams.get("fromDate");
    Date toDate = (Date) optionalParams.get("toDate");
    if (match == null) {
        match = new BasicDBObject("requestDate", new BasicDBObject("$gte", fromDate).append("$lte", toDate));
    } else {
        match.append("requestDate", new BasicDBObject("$gte", fromDate).append("$lte", toDate));
    }   
}
DBObject project = new BasicDBObject("$project", MongoDBUtil.getProjectDateFields());
DBObject groupFields = new BasicDBObject("_id", MongoDBUtil.getGroupDateFields()).append("total", new BasicDBObject("$sum", 1));
DBObject group = new BasicDBObject("$group", groupFields);
DBObject sort = new BasicDBObject("$sort", new BasicDBObject("_id", 1));
List<DBObject> pipeline;
if (match != null) {
    pipeline = Arrays.asList(match, project, group, sort);
} else {
    pipeline = Arrays.asList(project, group, sort);
}

Upvotes: 0

Views: 901

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151162

In brief, you broke your $match pipeline stage construction because like all pipeline directives that "key" is "mandatory. Your conditional building should not be checking is the existing state is null but rather "appending" to what you need to define at the top as a "empty" $match:

// BasicDBObject match = null; // wrong!
BasicDBObject match = new BasicDBObject("$match", new BasicDBObject()); // correct!
if (organizationId != null) {
    match.append("organizationId", organizationId);
}
if (optionalParams != null) {
    Date fromDate = (Date) optionalParams.get("fromDate");
    Date toDate = (Date) optionalParams.get("toDate");
    match.append("requestDate", new BasicDBObject("$gte", fromDate)
        .append("$lte", toDate));
}

So then, without a value for organizationId you get a pipeline generated like so:

{ "$match": { 
    "requestDate" : { 
        "$gte" : { "$date" : "2015-03-01T05:00:00.000Z" },
        "$lte" : { "$date" : "2015-03-09T04:00:00.000Z" }
    }
}

Without that $match key this is not a valid pipeline stage and it will error on submission to the aggregation pipeline.

There is nothing wrong with the generated syntax and all, full explanation is in the manual under Extended JSON. The MongoShell uses "strict" mode as mentioned there so it requires it's own wrappers.

Upvotes: 1

Related Questions