How to aggregate by Month, Week and Day

I've collection with tickets, I need to get number of tickets sold on Day, Week and Month. Below is the structure

{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 100,
    "createdAt": "2021-11-08T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 101,
    "createdAt": "2021-11-07T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 102,
    "createdAt": "2021-11-06T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 103,
    "createdAt": "2021-11-05T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 104,
    "createdAt": "2021-11-04T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 105,
    "createdAt": "2021-11-03T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 106,
    "createdAt": "2021-11-02T08:34:39.697 00:00"
},
{
    "_id": "9917aed79fcc274bdefgj",
    "ticketId": 107,
    "createdAt": "2021-11-01T08:34:39.697 00:00"
}

Im new to MongoDB, I tried using project or match to export the day, week or month however Im unsuccessful and hence could not share much of my work. Appreciate any help

CodePudding user response：

Test Here

db.collection.aggregate([
  {
    "$project": {
      "createdAtWeek": {
        "$week": "$createdAt"
      },
      "createdAtMonth": {
        "$month": "$createdAt"
      },
      "createdAtDay": {
        "$dayOfMonth": "$createdAt"
      }
    }
  },
  {
    "$group": {
      "_id": {
        createdAtWeek: "$createdAtWeek",
        createdAtMonth: "$createdAtMonth",
        createdAtDay: "$createdAtDay"
      },
      count: {
        $sum: 1
      }
    }
  }
])

CodePudding user response：

Query

facet is like multiple aggregations in 1 query
"year" => group by year and sum
"month" => group by year and month of the year and sum
"week" => group by year and week of the year and sum
"day" => group by day and day of the year and sum
concat all those arrays, unwind and replace root

and now we have documents like

[{"count": 8,"year": 2021},
{"count": 8,"year": 2021,"month": 11},
{"count": 2,"year": 2021,"week": 45},
{"count": 1,"year": 2021,"day": 307},
...
]

We can now do the match with any year/month of year/week of year/day of year, for example
for year 2021 we need to find the document that has year but not day and not week and not month (year only)
for the month its similar we need to match year and the month
for the week we cant calculate by hand the week, but we can use a date we want its week, and match with that, on this query i added a match to find the tickets sold in the week of the date ISODate("2021-11-07T08:34:39.697Z")
same for the day like the week

*this numbers are calender based not on durations, for example if it 1st of month, you will get the tickets sold only for 1 day.

*if you care only for a week even if this is from another year, remove from group, the year, because this query, separates weeks from different years (i guess this is what you wanted but not sure)

Test code here
(mongoplayground loses order of fields sometimes test also on your driver)

aggregate(
[{"$facet": 
    {"year": 
      [{"$group": 
          {"_id": {"year": {"$year": "$createdAt"}}, "count": {"$sum": 1}}},
        {"$project": {"_id": 0, "year": "$_id.year", "count": 1}}],
      "month": 
      [{"$group": 
         {"_id": 
           {"year": {"$year": "$createdAt"}, "month": {"$month": "$createdAt"}},
            "count": {"$sum": 1}}},
        {"$project": 
          {"_id": 0, "year": "$_id.year", "month": "$_id.month", "count": 1}}],
      "week": 
      [{"$group": 
          {"_id": 
            {"year": {"$year": "$createdAt"}, "week": {"$week": "$createdAt"}},
            "count": {"$sum": 1}}},
        {"$project": 
          {"_id": 0, "year": "$_id.year", "week": "$_id.week", "count": 1}}],
      "day": 
      [{"$group": 
          {"_id": 
            {"year": {"$year": "$createdAt"},
              "day": {"$dayOfYear": "$createdAt"}},
            "count": {"$sum": 1}}},
        {"$project": 
          {"_id": 0, "year": "$_id.year", "day": "$_id.day", "count": 1}}]}},
  {"$set": 
    {"sums": {"$concatArrays": ["$year", "$month", "$week", "$day"]},
      "year": "$$REMOVE",
      "month": "$$REMOVE",
      "week": "$$REMOVE",
      "day": "$$REMOVE"}},
  {"$unwind": {"path": "$sums"}},
  {"$replaceRoot": {"newRoot": "$sums"}},
  {"$match": 
   {"$expr": 
    {"$and": 
     [{"$eq":["$year",{"$year":ISODate("2021-11-07T08:34:39.697Z")}]},
      {"$eq":["$week",{"$week":ISODate("2021-11-07T08:34:39.697Z")}]}]}}}])