MongoDB nested group?

Question

I'm trying to implement a nested group query in mongodb and I'm getting stuck trying to add the outer group by. Given the below (simplified) data document:

{
  "timestamp" : ISODate(),
  "category" : "movies",
  "term" : "my movie"
}

I'm trying to achieve a list of all categories and within the categories there should be the top number of terms. I would like my output something like this:

[
 { category: "movies", 
   terms: [ { term: "movie 1", total: 5000 }, { term: "movie 2", total: 200 } ... ]
 },
 { category: "sports", 
   terms: [ { term: "football 1", total: 4000 }, { term: "tennis 2", total: 250 } ... ]
 },
]

My 'inner group' is as shown below, and will get the top 5 for all categories:

db.collection.aggregate([
    { $match : { "timestamp": { $gt: ISODate("2014-08-27") } } },
    { $group : { _id :  "$term", total : { $sum : 1 } } },
    { $sort : { total : -1 } },
    { $limit: 5 }
]);

// Outputs:
{ "_id" : "movie 1", "total" : 943 }
{ "_id" : "movie 2", "total" : 752 }

How would I go about implementing the 'outer group'?

Additionally sometimes the above aggregate]ion returns a null value (not all documents have a term value). How do I go about ignoring the null values?

thanks in advance

Answer 1

You will need two groups in this case. The first group generates a stream of documents with one document per term and category:

 { $group : { 
      _id :  { 
        category: "$category",
        term: "$term",
      },
      total: { $sum : 1 } 
   }
 }

A second group will then merge all documents with the same term into one, using the $push operator to merge the categories into an array:

 { $group : { 
      _id :  "$_id.category",
      terms: { 
          $push: { 
              term:"$_id.term",
              total:"$total"
          }
      }
   }
 }