0

I'm having a collection named revisions and it contains following data,

{
   "_id" : NumberLong(1007),
   "createdDate" : ISODate("2017-09-25T14:00:53.020Z"),
   "name" : "John",
   "status" : 700
}

I want to filter data for a given status and date(only date ignore time) range to generate reports. I'm using Jaspersoft studio. I have created two parameters fromDate and toDate. I've set up the parameters to parse as java.util.Date. I'm giving values for these parameters by a datepicker.

Here are some queries that I've tried,

1)
{
  "collectionName":"revisions",
  "findQuery":{
                "status":700,
                "createdDate": {"$gte" : $P{fromDate}},
                "createdDate": {"$lte" : $P{toDate}}
  }
}

2)
{
  "collectionName":"revisions",
   "findQuery":{ "status": 700, "createdDate": { "$gte": $P{fromDate}, "$lte": $P{toDate} } }
}

3)
{
  "collectionName":"revisions",
   "findQuery":{
             "status" : 700,
              $and: [ { "createdDate" : { '$gte' : $P{fromDate}}}, 
{"createdDate" : { '$lte' : $P{toDate}}} ] 
    }
}

There are similar questions like this[1] [2], but I couldn't find a solution, maybe something wrong with the way I've used parameters in my query or is there something to do with the ISODate type.

Alex K
  • 22,315
  • 19
  • 108
  • 236
Isuru
  • 129
  • 7
  • Have you actually [set up the date parameters to parse as `java.util.Date`?](https://community.jaspersoft.com/wiki/how-query-mongo-isodate-data-parameter). Unless you do that, then what you are sending is still a "string" and will not match the stored BSON Date. – Neil Lunn Sep 14 '17 at 00:55
  • Also your query is incorrect. Once you set up the correct date parsing you need `{ "status": 700, "createdDate": { "$gte": $P{fromDate}, "$lte": "$P{toDate} } }`. Your present query "overwrites" the key value for `"createdDate"` by specifying it twice. The correct syntax as shown is equivalent to an **AND** condition, by applying both `$gte` and `$lte` to the same property within the same object block. Just as is shown on [Find objects between two dates MongoDB](https://stackoverflow.com/questions/2943222/find-objects-between-two-dates-mongodb) and various samples in the core documentation – Neil Lunn Sep 14 '17 at 00:59
  • @NeilLunn yes, I've set up the parameters to parse as java.util.Date, Is there something to do with ISODate type with mongodb, I'm really new to mongdb I've no idea about ISODate format – Isuru Sep 14 '17 at 01:00
  • Show your actual configuration and also **read the other comment**. Your query syntax is incorrect. – Neil Lunn Sep 14 '17 at 01:01
  • `net.sf.jasperreports.engine.JRException: com.mongodb.util.JSONParseException: ` – Isuru Sep 15 '17 at 03:10
  • @NeilLunn above given query gives an exception – Isuru Sep 15 '17 at 03:11

0 Answers0