Cool Reporting on MongoDB

Interesting Reporting on MongoDB

Before you can do interesting reporting, you need to do simple reporting. You have to walk before you can run. Please start with the article Simple Reporting on MongoDB. That article explains where to get the MongoDB connector for iReport and JasperReports Server. When you have completed that, then read this article more advanced and more interesting reporting techniques.

Simple Filters

Let’s start with a simple hard-coded filter.

{
  'collectionName' : 'accounts',
  'findQuery' : { 'name' : 'M & Y Takemura Communications, Ltd'  }
}

Filtering on a single value makes sense when that value is a complex document. There could be lots of information in that document to create a report. To make this truly interesting though, we need this query to be parameterized. So our first truly realistic sample query is the following:

{
  'collectionName' : 'accounts',
  'findQuery' : { 'name' : '$P{CUSTOMER_NAME}'  }
}

Field Selection and Dot Notation

Documents in MongoDB can be big. The queries above will return every field in the document. When this is not appropriate, it’s easy to create a query to return only the fields that you need.

Imagine the case where we have lots of users and we want a report showing which users had logged certain events. There could be lots and lots of events, but there’s only one particular event that I care about in this report. Rather than return the complete document for each user we instead return only the fields we care about.

{
  'collectionName': 'active_users',
  'findFields' : { 'id':1, 'date':1, 'events.ACHIEVEMENT_Minor':1 }
}

Enter this query into iReport’s query field, and then iReport is able to execute the query and return only the fields we’re interested in.

The query editor makes it easy to test query syntax and to see query results

We could then combine this with a filter if we only want certain users returned.

{
  'collectionName': 'active_users',
  'findFields' : { 'id':1, 'date':1, 'events.ACHIEVEMENT_Minor':1 },
  'findQuery' : { 'events.ACHIEVEMENT_Major' : 1 }
}

Joins!

Joins? MongoDB doesn’t support joins. In general your data should be modeled so that you don’t need joins. If you need them, then perhaps you should be using a traditional RDBMS instead of MongoDB. But what about simple joins that are required in some types of reports? Subreports can be used in JasperReports to effectively join two collections together.

Imagine a collection that stores summary data by country and a collection that stores customers. I want to query the country collection to find my top countries for some time period and then query my customers collection to find the top customers in the those countries.

It’s simple in JasperReports. We create a query in the top level report to return the countries that I want. Then for each country I run a subreport that returns the customers from that country.  Here’s a simple version.

Parent query:

{ ‘collectionName’:'countries’ }

The subreport query finds all accounts that are in that country and whose name starts with “B”. (It’s a silly example. Why would I want companies that start with “B”? But I wanted to show the syntax for Regular Expressions in queries.)

{
	'collectionName':'accounts',
	'findFields':{'name':1,'phone_office':1,'billing_address_city':1,'billing_address_street':1,'billing_address_country':1},
	'sort':{'billing_address_city':1,'name':1},
	'findQueryRegEx':{'billing_address_country':'/$P{AccountCountry}/','name':'/^B /'}
}

The above set of two queries gives me exactly what I want. It has the drawback of running “N + 1″ queries. By that I mean that runs the subreport query once for each country. In some cases that could be bad. In these cases it could be even better to use the “IN” syntax so that I only need to run each query once.

I can use the same parent query. But rather than pass the countries one at a time to the subreport we can pass the complete list of countries as a Collection. Then the subreport can run a single query to get all of the accounts that I want. The working reports are attached below, but here’s the query to show the idea.

{
	'collectionName':'accounts',
	'findFields':{'name':1,'phone_office':1,'billing_address_city':1,'billing_address_street':1,'billing_address_country':1},
	'sort':{'billing_address_country':1,'billing_address_city':1,'name':1},
	'findQuery':{ 'billing_address_country': {'$in':$P{AccountCountryCollection}} }
}

Summary

MongoDB has more flexibility than most of today’s Big Data databases in its query syntax. That’s one thing that made it attractive as a reporting target for Jaspersoft. Hopefully this article gives a good idea of some of the possibilities that exist for reporting directly against a MongoDB data source.

Sample reports using all of the above queries are available here.

Advertisement