Querying date ranges in embedded array JAVA

[Hernán Camilo]

Hi.

I'm having problems trying to query a collection where two fields, start and end, are dates. In mongo, they are stored as ISODate("2016-07-30T19:16:01.001Z") and when I try to query them from a java client, the objects utilized keep putting the unknown operator "$date" and the query does not return anything.

I know that using the shell I can do this:

db.festivities.find({ "festivity" : { "$elemMatch" : { "start" : { "$gte" : ISODate("2015-09-30T20:16:01.001Z")} , "end" : { "$lte" : ISODate("2016-07-30T19:16:01.001Z")}}}}).pretty()

But I cannot translate this to Java. I have this:

DBObject searchCriteria = new BasicDBObject();
searchCriteria.put("start", startDate);
searchCriteria.put("end", endDate);
       
DBObject elemMatchQuery = new BasicDBObject("$elemMatch", searchCriteria);
       
DBObject fields = new BasicDBObject();
fields.put("festivity", elemMatchQuery);
System.out.println(fields.toString());
       
DBCursor carCursor = coll.find(fields);

But that query translates to:

{ "festivity" : { "$elemMatch" : { "start" : { "$date" : "2015-09-30T20:16:01.001Z"} , "end" : { "$date" : "2017-07-30T19:16:01.001Z"}}}}

So there are two problems: operator "$date" and the value of the query strings: they don't have "ISODate" so it won't return any results.

Any ideas would be very much appreciated.


Thanks.

[Wan Bachtiar]

I know that using the shell I can do this:

db.festivities.find({ “festivity” : { “gte” : ISODate(“2015-09-30T20:16:01.001Z”)}

, “end” : { “$lte” : ISODate(
“2016-07-30T19:16:01.001Z”)}}}}).pretty()
But I cannot translate this to Java.

Hi Hernan,

If the variable startDate and endDate in your example are in String, then you could utilise SimpleDateFormat to parse to date. For example:

SimpleDateFormat format =  new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", Locale.ENGLISH);
Date startDate = new Date(); 
Date endDate = new Date();

try {
   startDate = format.parse("2015-09-30T20:16:01.001Z");  // your string start value
   endDate = format.parse("2016-07-30T19:16:01.001Z"); // your string end value
} catch (ParseException e){
    // handle parsing error accordingly
    e.printStackTrace(); 
}
Document searchCriteria = new Document("festivity", 
                              new Document("$elemMatch", 
                                            new Document("$gte",startDate)
                                                .append("$lte", endDate)
                              )
                 );
MongoCursor<Document> cursor = collection.find(searchCriteria).iterator();

The example above utilises MongoDB Java Driver v3.0+.

Worth noting that in your Java code example, you are not including $gte and $lte operators in the query for the dates.

If the above doesn’t answer your question, please provide:

• MongoDB Java Driver version.
• The type and values of variables startDate and endDate

Regards,

Wan.

​