CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate-functions
SuperSecretAndNotSafeFromWork
SuperSecretAndNotSafeFromWork

Reputation: 1311

Get isodate from week integer in aggregate query | From 1 get 30/12/2019, from 2 get 06/01/2020

I have some data that i'd like to plot on charts grouped by week. The dates for the documents are ISODates:

"time_initialized" : ISODate("2019-04-02T11:52:55.464Z")

I'd like to group all these documents by week, but not as an integer as it doesn't look nice on the x-axis of a chart

I have this aggregation query:

{
  "$project": {
    "_id": 0,
    "referralid": 1,
    "time_initialized": 1,
    "week": {
      "$week": "$time_initialized"
    }
  }
},
{
  "$group": {
    "_id": "$week",
    "chats_opened": {
      "$sum": 1
    }
  }
},
{
  "$project": {
    "_id": 0,
    "week": "$_id",
    "chats_opened": 1
  }
},
{
  "$sort": {
    "week": 1
  }
}

Which outputs:

{ "chats_opened" : 8, "week" : 13 }
{ "chats_opened" : 1, "week" : 31 }
{ "chats_opened" : 6, "week" : 32 }
{ "chats_opened" : 2, "week" : 38 }
{ "chats_opened" : 1, "week" : 41 }

What i'd like:

chats_opened: 8, ISODate("2019-03-25T00:00:00.000Z")

Where the date is the first Monday of that week

Has anyone got a smart and concise way of achieving this?

Many thanks

Upvotes: 1

Views: 60

Answers (1)

mickl
mickl

Reputation: 49975

You can use $dateFromParts operator and pass your week as a parameter:

db.collection.aggregate([
    {
        $addFields: {
            week: {
                $dateFromParts: {
                    "isoWeekYear": 2019,
                    "isoWeek": "$week",
                    "isoDayOfWeek": 1
                }
            }
        }
    }
])

Mongo Playground

Upvotes: 1

Related Questions