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:
