Group by date in MongoDB

Question

I'm running a blog-style web application on AppFog (ex Nodester). It's written in NodeJS + Express and uses Mongoose framework to persist to MongoDB.

MongoDB is version 1.8 and I don't know whether AppFog is going to upgrade it to 2.2 or not.

Why this intro? Well, now my "posts" are shown in a basic "paginated" visualization, I mean they're just picked up from mongo, sorted by date descending, a page at a time. Here's a snippet:

Post
                .find({pubblicato:true})
                .populate("commenti")
                .sort("-dataInserimento")
                .skip(offset)
                .limit(archivePageSize)
                .exec(function(err,docs) {
                    var result = {};
                    result.postsArray = (!err) ? docs : [];
                    result.currentPage = currentPage;
                    result.pages = howManyPages;
                    cb(null, result);
            });

Now, my goal is to GROUP BY 'dataInserimento' and show posts like a "diary", I mean:

1st page => 2012/10/08: I show 3 posts

2nd page => 2012/10/10: I show 2 posts (2012/10/09 has no posts, so I don't allow a white page)

3rd page => 2012/10/11: 35 posts and so on...

My idea is to get first the list of all dates with grouping (and maybe counting posts for each day) then build the pages link and, when a page (date) is visited, query like above, adding date as parameter.

SOLUTIONS:

• Aggregation framework would be perfect for that, but I can't get my hands on that version of Mongo, now

• Using .group() in some way, but the idea it doesn't work in sharded environments does NOT excite me! :-(

• writing a MAP-REDUCE! I think this is the right way to go but I can't imagine how map() and reduce() should be written.

Can you help me with a little example, please?

Thanks

EDIT :

The answer of peshkira is correct, however, I don't know if I need exactly that.

I mean, I will have URLs like /archive/2012/10/01, /archive/2012/09/20, and so on.

In each page, it's enough to have the date for querying for posts. But then I have to show "NEXT" or "PREV" links, so I need to know what's the next or previous day containing posts, if any. Maybe can I just query for posts with dates bigger or smaller than the current, and get the first one's date?

Answer 1

Assuming you have something similar as:

{
"author" : "john doe",
"title" : "Post 1",
"article" : "test",
"created" : ISODate("2012-02-17T00:00:00Z")
}
{
"author" : "john doe",
"title" : "Post 2",
"article" : "foo",
"created" : ISODate("2012-02-17T00:00:00Z")
}
{
"author" : "john doe",
"title" : "Post 3",
"article" : "bar",
"created" : ISODate("2012-02-18T00:00:00Z")
}
{
"author" : "john doe",
"title" : "Post 4",
"article" : "foo bar",
"created" : ISODate("2012-02-20T00:00:00Z")
}
{
"author" : "john doe",
"title" : "Post 5",
"article" : "lol cat",
"created" : ISODate("2012-02-20T00:00:00Z")
}

then you can use map reduce as follows:

Map

It just emits the date as key and the post title. You can change the title to the _id, which will probably be more useful to you. If you store the time of the date you will want to use only the date (without time) as the key, otherwise mongo will group by date time and not only date. In my test case all posts have the same time 00:00:00 so it does not matter.

function map() {
  emit(this.created, this.title);
}

Reduce

It does nothing more, then just push all values for a key to an array and then the array is wrapped in a result object, because mongo does not allow arrays to be the result of a reduce function.

function reduce(key, values) {
  var array = [];
  var res = {posts:array};
  values.forEach(function (v) {res.posts.push(v);});
  return res;
}

Execute

Using db.runCommand({mapreduce: "posts", map: map, reduce: reduce, out: {inline: 1}}) will output the following result:

{
"results" : [
    {
        "_id" : ISODate("2012-02-17T00:00:00Z"),
        "value" : {
            "posts" : [
                "Post 2",
                "Post 1"
            ]
        }
    },
    {
        "_id" : ISODate("2012-02-18T00:00:00Z"),
        "value" : "Post 3"
    },
    {
        "_id" : ISODate("2012-02-20T00:00:00Z"),
        "value" : {
            "posts" : [
                "Post 5",
                "Post 4"
            ]
        }
    }
],
...
}

I hope this helps