0

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

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 JasperReports (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, 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": {}
})

Here goes a response example for the second query:

{
    "cursor" : {
        "firstBatch" : [ ],
        "id" : NumberLong(0),
        "ns" : "reports"
    },
    "ok" : 1
}

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.

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, JasperReports Studio: 6.5.1 final

For any suggestions thanks in advance!

Jacek
  • 194
  • 1
  • 11
  • As noted with the duplicate, your approach is just wrong. You don't "coerce a date to a day" but instead "provide the range of dates". A single day is still a "range", beginning at the start of day and ending at the next day. – Neil Lunn May 23 '18 at 01:30
  • Also you need to stop thinking in terms of "difference with runCommand". There is no difference at all save you are using something with a broken API which is only calling the invocation which does not return results at a "cursor". That's it, and it makes no difference to the rest of the processing. – Neil Lunn May 23 '18 at 01:32
  • @NeilLunn I have edited my question. Querying with `ISODate` returns results, while querying for the same date with `$date` yields 0 results. It does NOT look like a date range issue. It looks like `$date` not being correctly used for ISO dates in aggregate queries. – Jacek May 23 '18 at 04:32
  • It depends on what Jasper accepts. If it accept JavaScript expressions then `new Date("2017-01-29")` is generally acceptable. There's likely a question around which used Jasper for something similar before, but the same principle applies to use a "date range" rather then trying to force projection through aggregation pipelines. – Neil Lunn May 23 '18 at 04:41
  • FYI. My very first search result pulls up this page in the Jasper forums: https://community.jaspersoft.com/questions/818338/new-date-mongodb-query Which also links to here : https://community.jaspersoft.com/wiki/how-query-mongo-isodate-data-parameter .In short the `$date` from extended JSON format is correct, so you must still have the query incorrect. See the referenced question again for the correct syntax. – Neil Lunn May 23 '18 at 04:44
  • I am aware of these posts on JR forums and wiki. However, these are irrelevant here as I'm executing above queries in MongoDB shell. – Jacek May 23 '18 at 05:05
  • @NeilLunn given my comment above, I would expect you to unlock the question and remove the "duplicate" mark. This will let others chime in. – Jacek May 23 '18 at 19:38

0 Answers0