MongoDB aggregation via db.runCommand(): $match by date

[Jacek]

Hi all!

I need to match documents by date in an aggregation query executed in "strict JSON" mode via db.runCommand(). These requirements are dictated by the way JasperReports communicates with MongoDB (however, I don't believe it is relevant to the case).

Document schema:

{
    "_id" : ObjectId("5a6fca214420c2302a6f8985"),
    "report_date" : ISODate("2018-01-23T00:00:00.000+0000"),
    ...
}

This works in MongoDB console (returns matching documents), but it doesn't work in my main use case - JasperReports (which is throwing com.mongodb.util.JSONParseException
in the line containing the "ISODate()" command). I'm showing only the first stage of the pipeline for clarity:

db.runCommand(
{
    "aggregate" : "reports",
    "pipeline" : [
        {
            "$match": {
                "report_date": {
                    "$gte": ISODate("2017-01-23")
                }
            }
        }
    ],
    "cursor": {}
})

The same query using $date executes well in both MongoDB (and JasperReports as well), but does not return any documents (0 matches in both scenarios):

db.runCommand(
{
    "aggregate" : "reports",
    "pipeline" : [
        {
            "$match": {
                "report_date": {
                    "$gte": {"$date": "2017-01-23T00:00:00.000Z"}
                }
            }
        }
    ],
    "cursor": {}
})

I observe the same behavior in range queries, like in the following snippet:

...
"$gte": {"$date": "2017-01-29T00:00:00.000Z"},
"$lt": {"$date": "2019-01-29T00:00:00.000Z"}
...

I also tried using $dateFromString instead of $date(interestingly, using this one in MongoDB 3.2 didn't result in any exceptions being thrown) - same result.

Unless I'm missing something obvious here, it almost looks like you can't use $date for querying ISO dates.
How can I run queries by date or date ranges in the "match" stage of aggregation pipeline and strict-JSON mode?

Tested versions: MongoDB 3.2.12 and 3.6.2

For any suggestions thanks in advance!

[Kevin Adistambha]

Hi Jacek

Recent Jasper Report versions use MongoDB Java Driver version 2.7.3, which is quite outdated (current Java driver is version 3.7) so it’s possible that some queries are not translated as per newer MongoDB server versions. Notably, the Java driver 2.7.3 is not listed anymore in Java Driver compatibility list due to its age.

There are two things you could try:

1. Try running with an elevated log level (e.g. db.setLogLevel(2, 'query')) or elevated profiling level to determine the actual query passed on to MongoDB by the driver.

2. There is a short example on the Jasper Report page regarding date queries that you may be able to try:

{
     'collectionName' : 'accounts',
     'findQuery' : {
          'status_date' : { '$gte' : $P{StartDate}, $lt: $P{EndDate} },
          'name' : { '$regex' : '^N', '$options' : '' }

      }
}

Other than that, I would recommend you to ask in the JasperSoft community regarding this query.

Best regards
Kevin

​