Group By Count With MongoDB And MongooseJS


In my last post about reports, I showed a simple script that gave me a count of customers. I used a similar query in SignalLeaf to give me the count of listens / downloads for episodes, too. This gave me a very basic traffic report for how many episodes had been served up, total. But the raw count feature of MongooseJS / MongoDB doesn’t give me any kind of detail. It literally just returns a count of the things that match the query parameters. What I really want to see is a list of episodes with a total # of plays for each episode.

Group-By

The challenge that I have in getting this data is that I am tracking downloads/listens in a separate MongoDB collection. I have a Podcasts collection and each Podcast has Episodes. Then I have a Tracking collection, where each Tracking instance contains a podcastId and episodeId to tell me which podcast/episode is being tracked. I have some other data in there, too, but it isn’t relevant right now.

I knew I needed to group the Tracking table by episodeId to start with, so I called on the aggregate function to do this.

This gives me back a list of unique episodeId entries in the Tracking collection – that’s the first problem solved. But now I need to get a count of each episodeId in the Tracking collection as well.

Group By Count – Failures

At first I thought I was going to have to loop through the list of episode IDs and make a separate call for each. That would be a horrible performance nightmare, though. So I tried to add a total value to the grouped output using a

This failed miserably because the group aggregation operators does not support $count. It looked like I was back to iterating and querying for each for a moment. Then I had a thought that seemed kind of stupid but might work.

Sum Of 1 As A Counter

I’ve used the $sum function to get totals in another place within SignalLeaf. Why not use that for my count, by doing a sum of a hard coded value of 1? The configuration of the group query options is a JavaScript object, after all, so I’ll just hard code 1 in to the value instead of using a string to tell it which field to sum.

And it worked!

The Result: Group By Count For MongoDB

Each entry for a given episode ID was given a value of “1” which was added to all of the other entries, creating a group-by count result in MongoDB! And I now I have a fancy little report that shows me totals per episode, along with the total traffic for episode deliveries:

Screen Shot 2013 10 28 at 5 09 59 PM

Tracking Errors In Production: I Panicked And Grabbed My Credit Card