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. 

  var agg = [
    {$group: {
      _id: "$episodeId"
    }}
  ];

  Tracking.aggregate(agg, function(err, logs){
    if (err) { return def.reject(err); }

    console.log(logs);
  });

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 

  var agg = [
    {$group: {
      _id: "$episodeId",
      
      // FAIL!!!! :(
      total: {$count: "episodeId"}
    }}
  ];

  Tracking.aggregate(agg, function(err, logs){
    if (err) { return def.reject(err); }

    console.log(logs);
  });

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.

  var agg = [
    {$group: {
      _id: "$episodeId",
      
      // SUCCESS!!! :D
      total: {$sum: 1}
    }}
  ];

  Tracking.aggregate(agg, function(err, logs){
    if (err) { return def.reject(err); }

    console.log(logs);
  });

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


Post Footer automatically generated by Add Post Footer Plugin for wordpress.

About Derick Bailey

Derick Bailey is an entrepreneur, problem solver (and creator? :P ), software developer, screecaster, writer, blogger, speaker and technology leader in central Texas (north of Austin). He runs SignalLeaf.com - the amazingly awesome podcast audio hosting service that everyone should be using, and WatchMeCode.net where he throws down the JavaScript gauntlets to get you up to speed. He has been a professional software developer since the late 90's, and has been writing code since the late 80's. Find me on twitter: @derickbailey, @mutedsolutions, @backbonejsclass Find me on the web: SignalLeaf, WatchMeCode, Kendo UI blog, MarionetteJS, My Github profile, On Google+.
This entry was posted in Command Line, Design, Javascript, MongoDB, MongooseJS, New Category, Reporting, SignalLeaf. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • Opsy

    You linked to the docs so it’s actually conceivable you might have read some of the examples there – they say to use {$sum:1} to get count…

    • http://mutedsolutions.com Derick Bailey

      *facepalm*

      oops :)

  • Bastin Robin

    How to get other fields with this same query?