4

I'm very new to MongoDB and mongoose and I'd like to get the total number of subscribers from start date to current date by day. In other words, I need to group all my subscribers by day, and for each day I need to sum all of my previous subscribers.

Here are some of my my documents:

{ "_id" : ObjectId("574cef8ee62502d08f34c075"), "userId" : "a7zd609h54wm5kt", "subscribedAt" : ISODate("2016-05-30T18:22:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.098Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c076"), "userId" : "3q2tvd53zcap5mq", "subscribedAt" : ISODate("2016-05-30T19:52:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.113Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c077"), "userId" : "nvx8mnu1xis5jxt", "subscribedAt" : ISODate("2016-05-28T19:52:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.117Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c078"), "userId" : "l7eedg616r0zbdf", "subscribedAt" : ISODate("2016-05-28T16:28:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.122Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c079"), "userId" : "9r1wl8ao8bls7g7", "subscribedAt" : ISODate("2016-05-28T11:05:05Z"), "isSubscribed" : false, "createdAt" : ISODate("2016-05-31T01:57:34.125Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c07a"), "userId" : "ygwve2e47p7fanl", "subscribedAt" : ISODate("2016-05-29T00:28:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.125Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c07b"), "userId" : "1gxspx9jypwc9tu", "subscribedAt" : ISODate("2016-05-29T19:52:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.127Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c07c"), "userId" : "hvy4xjppos8ch2b", "subscribedAt" : ISODate("2016-05-29T01:36:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.127Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c07d"), "userId" : "bmpql2d7wkl0jnw", "subscribedAt" : ISODate("2016-05-29T21:50:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.127Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c07e"), "userId" : "uir99sy6q3p6i0i", "subscribedAt" : ISODate("2016-05-29T08:22:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.131Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c07f"), "userId" : "qmzn3rz308ku017", "subscribedAt" : ISODate("2016-05-29T22:02:05Z"), "isSubscribed" : false, "createdAt" : ISODate("2016-05-31T01:57:34.132Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c080"), "userId" : "ok46sxaf8urrqtj", "subscribedAt" : ISODate("2016-05-29T07:33:05Z"), "isSubscribed" : false, "createdAt" : ISODate("2016-05-31T01:57:34.132Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c081"), "userId" : "ot4nmxqsn4o98vn", "subscribedAt" : ISODate("2016-05-29T23:52:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.138Z"), "__v" : 0 }
{ "_id" : ObjectId("574cef8ee62502d08f34c082"), "userId" : "2voy5ttkk39i1f0", "subscribedAt" : ISODate("2016-05-30T00:52:05Z"), "isSubscribed" : true, "createdAt" : ISODate("2016-05-31T01:57:34.138Z"), "__v" : 0 }

So I've got 2 subscribers on 2016-05-28, 6 subscribers on 2016-05-29 and 3 subscribers on 2016-05-30,

The result I'm hoping to achieve is something like this

{
    "results" : [
        {
            "date" : ISODate("2016-05-28T00:00:00Z"),
            "subscribers" : 2
        },
        {
            "date" : ISODate("2016-05-29T00:00:00Z"),
            "subscribers" : 8
        },
        {
            "date" : ISODate("2016-05-30T00:00:00Z"),
           "subscribers" : 11
        },
    ]
}

I've tried Aggregation Framework with the following code:

var express = require('express'),
    router  = express.Router(),
    User    = require('../models/user');

router.get('/', function(req, res, next) {
    User.aggregate([
        {
            $match: { isSubscribed: true, subscribedAt: {$gte: new Date("2016-05-28T00:00:00+01:00"), $lte: new Date()}}
        }, 
        {
            $group: {
                _id: {$dateToString: { format: "%Y-%m-%d", date: "$subscribedAt" }},
                count : { $sum : 1 }
            },
        }, 
        {
            $project: {
                _id             : 0,
                subscribeDate   : "$_id",
                count           : 1
            }
        },
        {
            $sort: {subscribeDate: 1} 
        }
    ], function(err, result){
        if (err) {
            console.log("This is an error find the user!")
        }
        res.render('dashboard', {results: result, title: "Welcome to analytics dashboard!"});
    });

});
module.exports = router;

But this only gives me the total subscribers of each day, NOT the the sumup of all the previous subscribers.

{ "count" : 2, "subscribeDate" : "2016-05-28" }
{ "count" : 6, "subscribeDate" : "2016-05-29" }
{ "count" : 3, "subscribeDate" : "2016-05-30" }

I've also tried mapReduce as suggested here with the following code:

var express = require('express'),
    router  = express.Router(),
    User    = require('../models/user'),
    DailySubscriber    = require('../models/dailySubscriber');

router.get('/', function(req, res, next) {
    var userObject = {
        "query": {isSubscribed: true},
        "out": "dailySubscriber"
    };
    userObject.map = function () { 
        var date = new Date(this.subscribedAt.valueOf() - (this.subscribedAt.valueOf() % (1000 * 60 * 60 * 24 )));
        emit(date, 1) 
    }
    userObject.reduce = function (k, vals) { return vals.length }
    User.mapReduce(userObject, function (err, results) {
    //console.log(results)
    })

    var subscriberObject = {
        "scope": { "total": 0 },
        "finalize": function(key, value) {
            total += value;
            return total;
        },
        "out": "totalSubscriber"
    };

    subscriberObject.map = function () { 
        emit(this._id, this.value) 
    }
    subscriberObject.reduce = function (k, vals) { return vals.length }
    DailySubscriber.mapReduce(subscriberObject, function (err, total) {
        console.log("This is the result" + total)
    })

    res.render('dashboard', {title: "Welcome to analytics dashboard!"});
});
module.exports = router;

This did NOT work when I ran my node app and I got the error This is the result undefined in the console, BUT it did work in MongoDB Shell with the following result which is very close to what I want but still not so ideal, as it always shows _id and value as the key and value.

{
    "results" : [
        {
            "_id" : ISODate("2016-05-28T00:00:00Z"),
            "value" : 2
        },
        {
            "_id" : ISODate("2016-05-29T00:00:00Z"),
            "value" : 8
        },
        {
            "_id" : ISODate("2016-05-30T00:00:00Z"),
            "value" : 11
        },
    ]
}

What would be the best way of doing it? I'm using Node.js, Express and Mongoose, if that helps.

Any help would be much appreciated! Thanks in advance!

Community
  • 1
  • 1
lei he
  • 259
  • 3
  • 8

1 Answers1

0

imho - having aggregation framework to have a daily sum is best what we can squeeze from mongo. As (from sql world) we don't have CTE, we cannot refer to previous document and that makes some analytics hard.

What I will do in this case is just simple forEach loop and update the sum value creating moving Sum in this particular case. This will require that data need to be sorted from oldest to newest. A sudocode for that below:

var previous = 0;
forEach (var r in result)
{
    r.count += previous;
    previous = r.count;
}
profesor79
  • 9,213
  • 3
  • 31
  • 52