First Dates with MongoDB
Background
You need some software before you can make use of these ideas:
You should probably start by reading my previous articles before reading this one:
Date queries in MongoDB reports
This idea actually applies to all types of Java objects, not just Dates. But most data types likes Strings and simple numbers get converted magically to what you probably want. Dates are probably the most common object that is too complex to be handled by this automatic conversion process.
Let’s take what is perhaps the simplest possible example. I want a query that will return only the documents for which a specified field, status_date, is later than some date. It’s a simple JSON-style query in the MongoDbQuery language of the Jaspersoft MongoDB connector:
{
'collectionName' : 'active_users',
'findQuery' : { 'status_date' : {'$gte':$P{StartDate}} }
}
It’s easy enough to understand even if it’s a bit verbose with curly braces in curly braces in curly braces. My report has a java.util.Date parameter which gets passed into the query. In earlier versions of the connector—as pointed out by one astute reader—there was no way to do this. Attempts to embed syntax like this into the query resulted in JSON parsing exceptions: { ‘status_date’ : {‘$gte’: new ISODate(’2011-09-15T00:00:00.000Z’)’ } }
Sample Data
In case you’re relatively new to MongoDB, like me, it can smooth your path to testing to have a sample data set and sample report that are known to work well together. You can insert a these few documents on the MongoDB shell and then use the sample report listed below to get something that ought to work with minimal effort. Then you’re ready to start writing your own reports.
The first document is listed on many lines to make it easier to parse it by eye to see what’s in it. The second two documents are compressed onto a single line. The final “find” command is useful to confirm that the earlier inserts worked as expected.
db.active_users.save(
{
"status_date" : ISODate("2011-09-01T07:48:58Z"),
"id" : "123",
"events" : {
"ACHIEVEMENT_Minor" : 1,
"ACHIEVEMENT_Major" : 1,
"ACHIEVEMENT_Ultimate" : 0
},
"install_source" : {
"detail" : "campaign - Free iPad",
"vt1" : "email",
"vt2" : "facebook"
},
"rev" : {
"net_total" : 700
}
}
)
db.active_users.save({"status_date" : ISODate("2011-09-09T22:17:27Z"),"id" : "456","events" : {"ACHIEVEMENT_Minor" : 1,},"install_source" : {"detail" : "campaign - Free iPad","vt1" : "email","vt2" : "facebook"},"rev" : {"net_total" : 450}})
d = new Date()
db.active_users.save({"status_date" : d,"id" : "789","events" : {"ACHIEVEMENT_Minor" : 1,},"install_source" : {"detail" : "campaign - Like us on Facebook","vt1" : "email","vt2" : "facebook"},"rev" : {"net_total" : 222}})
db.active_users.find()
Reminder
The idea for this article (and for this new feature to the Jaspersoft MongoDB Connector) came from a reader comment. Please let me know when you run into questions, but for general questions about the MongoDB connector please post to the JasperReports Server forum instead of posting a comment here. That’s a better location for asking and answering questions.
Right Outer Join 