Mongoose aggregation groupby on day,week,year

Question

I am using nodejs and mongodb for database

My document is:

{
    "_id": "58466d6a0b3f4d2e2fa22905",
    "updatedAt": "2016-12-06T07:48:58.435Z",
    "createdAt": "2017-12-06T07:48:58.435Z",
    "userId": "56d04e265a2100c72a311334",
    "__v": 0
  }

User will give fromDate ,toDate and frequency in clientside in which frequency value can be 0,1,2 where 0 means day,1 means week,2 means year.

On basis of above data i want to generate report

for ex: if user give fromDate=2016-12-1 and toDate=2016-12-6.

if frequency is 0 the result would be

{
  2016-12-01:{
    count:1
          }
  2016-12-02:{
      count:7   
        }
  2016-12-06:{
      count:8
      }
  }

if frequency is 1 the result would be

{
  2016-12-01 to 2016-12-06:{
    count:16
          }
  }

if frequency is 2 the result would be

{
  2016:{
    count:16
          }
  }

In which count is number of documents

I have done like this:

var aggregate = [{
        $match: {
            createdAt: {
                $gte: fromDate,
                $lt: toDate
            }
        }
    }, {
        $group: {
            // what should i will do here
            },
            count: {
                $sum: 1
            }
        }
    }]

    return LoginReportModel.aggregate(aggregate).exec();

OR Any one know other better approach like using lodash also welcome

Edited: I also want count of documents to be unique on userId

Rudi · Answer 1 · 2016-12-06T14:21:42.447

4

Check out the documentation on $dateToString

You could do it like this:

var frequency = 2

var frequencyDateFormatMap = {
  0: '%Y-%m-%d',
  1: '%U',
  2: '%Y'
}

var frequencyDateFormat = frequencyDateFormatMap[frequency]

var aggregate = [
  {$match: {
    createdAt: {
      $gte: fromDate,
      $lt: toDate
    }
  }},
  {$project: {
    frequency: {
      $dateToString: {
        format: frequencyDateFormat,
        date: '$createdAt'
      }
    }
  }},
  {$group: {
    _id: '$frequency',
    count: {
      $sum: 1
    }
  }}
]

return LoginReportModel.aggregate(aggregate).exec()

edited Dec 06 '16 at 14:21

answered Dec 06 '16 at 13:27

Rudi

2,987
1
12
18

can you help me out on above problem – Simran Dec 06 '16 at 14:04
I added an example, the only difference is that for frequency 1 it outputs the week number as the group key instead of a string like "2016-12-01 to 2016-12-06". Hope it helps! – Rudi Dec 06 '16 at 14:24
Thanks for help! This thing i can also achieve with $dayOfMonth and $year.. but main issue is with week.. how can i convert this week number to date as i mentioned above because i hv to write above data in excel file to generate report – Simran Dec 07 '16 at 06:11
also wnats distinct values count on userId(Edited) – Simran Dec 07 '16 at 07:22

Mongoose aggregation groupby on day,week,year

1 Answers1