node.js - groups by month and year using mongoose.js

Question

my collection in mongodb looks like below:

{
    "AccountID" : "87f7fd60-d1ad-11e2-98bb-795730bce125",
    "userId" : ObjectId("51b59fbec46916e60d00000c"),
    "_id" : ObjectId("51b6e603e3efef161b000003"),
    "accessDate" : ISODate("2013-06-11T08:55:31.957Z"),
    "__v" : 0
}
{
        "AccountID" : "47f7fd60-d1ad-11e2-98bb-795730bce125",
        "userId" : ObjectId("51b59fbec46916e60d00000d"),
        "_id" : ObjectId("51b6e603e3efef161b000003"),
        "accessDate" : ISODate("2013-05-1T08:05:31.957Z"),
        "__v" : 0
}

i what to write a query which results the below result: this is result as grouped by month and year and the count per day.

{
  "usage": [
    {
      "year": 2013,
      "monthlyusage": [
        {
          "month": 1,
          "dailyusage": [
            {
              "day": 1,
              "count": 205
            },
            {
              "day": 2,
              "count": 1109
            },
            {
              "day": 4,
              "count": 455
            }
          ]
        },
        {
          "month": 2,
          "dailyusage": [
            {
              "day": 11,
              "count": 256
            },
            {
              "day": 2,
              "count": 1001
            },
            {
              "day": 5,
              "count": 65
            }
          ]
        }
      ]
    },
    {
      "year": 2012,
      "monthlyusage": [
        {
          "month": 12,
          "dailyusage": [
            {
              "day": 1,
              "count": 78
            },
            {
              "day": 2,
              "count": 7009
            },
            {
              "day": 28,
              "count": 55
            }
          ]
        },
        {
          "month": 11,
          "dailyusage": [
            {
              "day": 11,
              "count": 800
            },
            {
              "day": 2,
              "count": 5094
            },
            {
              "day": 25,
              "count": 165
            }
          ]
        }
      ]
    }
  ]
}

How can i do this using mongoose.js framework

Answer 1

Mongoose provides a lightweight wrapper around the MongoDB aggregation framework. If you're new to aggregation, you can learn more about in from the MongoDB docs: http://docs.mongodb.org/manual/aggregation/

To massage your data into the form you've described above, you can use an aggregation pipeline with a series of $group operations. Here it is using the mongoose framework:

var dateSchema = mongoose.Schema({…});
var DateItem = mongoose.model('DateItem', dateSchema);

DateItem.aggregate(
      { $group : { 
           _id : { year: { $year : "$accessDate" }, month: { $month : "$accessDate" },day: { $dayOfMonth : "$accessDate" }}, 
           count : { $sum : 1 }}
           }, 
      { $group : { 
           _id : { year: "$_id.year", month: "$_id.month" }, 
           dailyusage: { $push: { day: "$_id.day", count: "$count" }}}
           }, 
      { $group : { 
           _id : { year: "$_id.year" }, 
           monthlyusage: { $push: { month: "$_id.month", dailyusage: "$dailyusage" }}}
           }, 
      function (err, res)
           { if (err) ; // TODO handle error 
             console.log(res); 
           });
});

The first $group will result in documents of this form, one for each day:

{ 
  "_id" : { "year" : 2013, "month" : 8, "day" : 15 },
  "count" : 1
}

The second $group will result in documents grouped by month:

{
  "_id" : { "year" : 2012, "month" : 11 },
 "dailyusage" : [
          { "day" : 6, "count" : 1 },
          { "day" : 9, "count" : 1 },
          ... ]
},

And the third $group will result in even larger documents, one for each year.

This query will aggregate your data into large, hierarchical documents. If you plan to run queries on this data after aggregation, however, this might not be the most useful form for your data to be in. Consider how you'll be using the aggregated data. A schema involving more smaller documents, perhaps one per month or even one per day, might be more convenient.