Right Outer Join

22 September 2011

Date Queries in MongoDB Reports

Filed under: JasperReports Server — Tags: , , , — mdahlman @ 12:21

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()

Sample Report

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.

 

Advertisements

1 Comment »

  1. Hello, thank you very much for your articles. They are very useful.
    I have a question about the first query. How do you pass this parameter StartDate?

    And how can I make a comparison with actual date? Something like ‘fromDate’ : { ‘$lt’ : ISODate() }, which works in the standard mongoDB shell.
    Thank you in advance

    Comment by Diana — 22 May 2013 @ 00:52


RSS feed for comments on this post. TrackBack URI

Go on... leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: