Mongo: How to group by $week but return start date and end date of each week instead?

Question

I am using $group by $week. It's returning the #week in the year, but I want the start date, end date of each week instead of

Sample:

Current result:

[{
    "_id" : 20,
    "averageValue" : null
},
{
    "_id" : 21,
    "averageValue" : 28.1875
}]

Expect:

[{
    "_id" : [{ start: "2021-01-01", "end": "2021-01-07" }],
    "averageValue" : null
},
{
    "_id" : [{ start: "2021-01-08", "end": "2021-01-14" }],
    "averageValue" : 28.1875
}]

Answer 1

Here is another solution to your question. In this solution, I've used a (minimum date) date value to find the week start and week end. Please take a look at the code below:

db.collection.aggregate([
  {
    $unwind: "$history"
  },
  {
    $group: {
      _id: {
        "at": {
          $week: {
            date: "$at",
            timezone: "-05:00"
          }
        },
        "type": "$history.type"
      },
      minDate: {
        $min: "$at",
        
      },
      averageValue: {
        $avg: "$history.value",
        
      },
      minimumValue: {
        $min: "$history.value",
        
      },
      maximumValue: {
        $max: "$history.value",
        
      }
    }
  },
  {
    "$project": {
      "_id": "$_id.at",
      "type": "$_id.type",
      "averageValue": "$averageValue",
      "minimumValue": "$minimumValue",
      "maximumValue": "$maximumValue",
      "weekStart": {
        $dateToString: {
          format: "%Y-%m-%d",
          date: {
            // convert date
            $subtract: [
              "$minDate",
              {
                $multiply: [
                  {
                    $subtract: [
                      {
                        $isoDayOfWeek: "$minDate"
                      },
                      1
                    ]
                  },
                  86400000
                ]
              }
            ]
          }
        }
      },
      "weekEnd": {
        $dateToString: {
          format: "%Y-%m-%d",
          date: {
            // convert date
            $subtract: [
              "$minDate",
              {
                $multiply: [
                  {
                    $subtract: [
                      {
                        $isoDayOfWeek: "$minDate"
                      },
                      7
                    ]
                  },
                  86400000
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Mongo Playround example

Answer 2

Although it is doable, makes code less readable and more complex.

I will rewrite the $group stage to include year along with weeks and add the $project stage to format the data as per requirement.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "week": {
          "$week": {"$subtract": ["$at", 25200000]}  // <-- Changes timezone to -07:00
        },
        "year": {
          "$year": {"$subtract": ["$at", 25200000]}  // <-- Changes timezone to -07:00
        },
      },
      // <-- Add keys to be added in group along with its logics
      "averageValue": {
        "$avg": "$readings.level_1"
      }
    },
  },
  {
    "$project": {
      "_id": {
        "startDate": {
          "$dateToString": {
            "date": {
              "$dateFromParts": {
                "isoWeekYear": "$_id.year",
                "isoWeek": "$_id.week"
              }
            },
            "format": "%Y-%m-%d",
          },
        },
        "endDate": {
          "$dateToString": {
            "date": {
              "$add": [
                {
                  "$dateFromParts": {
                    "isoWeekYear": "$_id.year",
                    "isoWeek": "$_id.week"
                  }
                },
                518400000,
              ],
            },
            "format": "%Y-%m-%d",
          },
        },
      },
      // <-- Add remaining keys to be projected
      "averageValue": 1,
    },
  },
])

Let me know if you need an explanation of each stage and operator used and why I used it.

Mongo Playground Sample Execution