CODEWITHSUNDEEP
mongodbnosql-aggregation
IT Forever
IT Forever

Reputation: 43

How to aggregate by Month, Week and Day - MongoDB

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

Update:
Expected output when query for 08-Nov
Day: 1
Week: 1
Month: 8

Explanation of output:
Day: 1 — no. of tickets sold on the given date
Week: 1 — no. of tickets sold on the given week (starts from Monday, only 1 ticket was sold)
Month: 8 — no. of tickets sold on the given month, 8 tickets were sold

Upvotes: 0

Views: 1934

Answers (2)

Takis
Takis

Reputation: 8705

Query

  • put the date you want in the first set, if you want for the current date keep the $$NOW system variable that has the current date
  • 4x the same code for year/month/week/day
  • here filters before group, to have the same year or month or week or day with the $$NOW date which is the current date.

*i used $isoWeek for week to start on monday $week starts week at sunday, you can change them

PlayMongo

aggregate(
[{"$set": {"date": "$$NOW"}},
  {"$facet": 
    {"year": 
      [{"$match": 
          {"$expr": {"$eq": [{"$year": "$date"}, {"$year": "$createdAt"}]}}},
        {"$group": {"_id": null, "count": {"$sum": 1}}},
        {"$unset": ["_id"]}],
      "month": 
      [{"$match": 
          {"$expr": 
            {"$and": 
              [{"$eq": [{"$year": "$date"}, {"$year": "$createdAt"}]},
                {"$eq": [{"$month": "$date"}, {"$month": "$createdAt"}]}]}}},
        {"$group": {"_id": null, "count": {"$sum": 1}}},
        {"$unset": ["_id"]}],
      "week": 
      [{"$match": 
         {"$expr": 
           {"$and": 
            [{"$eq": [{"$year": "$date"}, {"$year": "$createdAt"}]},
             {"$eq": [{"$isoWeek": "$date"}, {"$isoWeek": "$createdAt"}]}]}}},
        {"$group": {"_id": null, "count": {"$sum": 1}}},
        {"$unset": ["_id"]}],
      "day": 
      [{"$match": 
          {"$expr": 
            {"$and": 
              [{"$eq": [{"$year": "$date"}, {"$year": "$createdAt"}]},
                {"$eq": 
                  [{"$dayOfYear": "$date"}, {"$dayOfYear": "$createdAt"}]}]}}},
        {"$group": {"_id": null, "count": {"$sum": 1}}},
        {"$unset": ["_id"]}]}},
  {"$set": 
    {"year": 
      {"$cond": 
        [{"$eq": ["$year", []]}, 0, {"$arrayElemAt": ["$year.count", 0]}]},
      "month": 
      {"$cond": 
        [{"$eq": ["$month", []]}, 0, {"$arrayElemAt": ["$month.count", 0]}]},
      "week": 
      {"$cond": 
      [{"$eq": ["$week", []]}, 0, {"$arrayElemAt": ["$week.count", 0]}]},
      "day": 
      {"$cond": 
        [{"$eq": ["$day", []]}, 0, {"$arrayElemAt": ["$day.count", 0]}]}}}])

Results
(for "date" 9 November with my data i got)

[
  {
    "day": 1,
    "month": 8,
    "week": 1,
    "year": 8
  }
]

Upvotes: 1

Pramisha C P
Pramisha C P

Reputation: 327

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
      }
    }
  }
])

Upvotes: 1

Related Questions