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