I am creating a query in MongoDB which works correctly for me but in the end I have to do an extra grouping to get the response grouped by year. This is the query:
db.sales.aggregate([
{
$group: {
_id: {
month: {
$month: "$createdAt"
},
year: {
$year: "$createdAt"
},
dayOfWeek: {
$dayOfWeek: "$createdAt"
},
stringDay: {
$dateToString:
{ format: "%Y-%m-%d", date: "$createdAt"}
},
week: {
$isoWeek: "$createdAt"
}
},
total: { $sum: '$total'},
count: { $sum: 1 },
totalAverage: { $avg: '$total'}
}
},
{
$sort : {
"_id.month" : 1
}
},
{
$project: {
total: { $round: [ "$total", 2 ] },
year: "$_id.year",
date: "$_id.date",
week: "$_id.week",
numVentas: "$count",
month: "$_id.month",
dayOfWeek: "$_id.dayOfWeek",
stringDay:"$_id.stringDay",
count: "$count",
totalAverage: { $round: [ "$totalAverage", 2 ] },
stringMonth: {
$arrayElemAt: [
[
"",
"Jan",
"Feb",
"Mar",
"Apr",
"May",
"Jun",
"Jul",
"Aug",
"Sep",
"Oct",
"Nov",
"Dec"
],
"$_id.month"
]
},
stringWeek: {
$switch: {
branches:[
{ case: { $eq: ["$_id.dayOfWeek", 1] }, then: "Lunes" },
{ case: { $eq: ["$_id.dayOfWeek", 2] }, then: "Martes" },
{ case: { $eq: ["$_id.dayOfWeek", 3] }, then: "Miércoles" },
{ case: { $eq: ["$_id.dayOfWeek", 4] }, then: "Jueves" },
{ case: { $eq: ["$_id.dayOfWeek", 5] }, then: "Viernes" },
{ case: { $eq: ["$_id.dayOfWeek", 6] }, then: "Sábado" },
{ case: { $eq: ["$_id.dayOfWeek", 7] }, then: "Domingo" }
],
default: "Día desconocido"
}
}
}
},
{
$group: {
_id: { month: "$stringMonth", year: "$year"},
count: { $sum: "$count" },
total: { $sum: "$total" },
totalAverage: { $sum: "$totalAverage" },
sales: {
$push: {
numberDay: "$dayOfWeek",
stringWeek: "$stringWeek",
date: "$stringDay",
total: "$total",
count: "$count",
totalAverage: { $round: [ "$totalAverage", 2 ] }
}
}
}
},
{
$group: {
_id: "$_id.year",
monthsWithSales: { $sum: 1 },
count: { $sum: "$count" },
total: { $sum: "$total" },
totalAverage: { $sum: "$totalAverage" },
sales: {
$push: {
mes: "$_id.month",
count: "$count",
total: "$total",
totalAverage: "$totalAverage",
sales:"$sales"
}
}
}
}
])
And I get this response:
[
{
"_id": 2022,
"monthsWithSales": 4,
"count": 57,
"total": 22324.8,
"totalAverage": 7765.799999999999,
"sales": [
{
"mes": "Oct",
"count": 10,
"total": 1936,
"totalAverage": 1233.6,
"sales": [
{
"numberDay": 6,
"stringWeek": "Sábado",
"date": "2022-10-21",
"total": 526.8,
"count": 3,
"totalAverage": 175.6
},
{
"numberDay": 1,
"stringWeek": "Lunes",
"date": "2022-10-02",
"total": 85.6,
"count": 1,
"totalAverage": 85.6
},
{
"numberDay": 7,
"stringWeek": "Domingo",
"date": "2022-10-22",
"total": 526.8,
"count": 3,
"totalAverage": 175.6
},
{
"numberDay": 3,
"stringWeek": "Miércoles",
"date": "2022-10-04",
"total": 180,
"count": 1,
"totalAverage": 180
},
{
"numberDay": 4,
"stringWeek": "Jueves",
"date": "2022-10-12",
"total": 531.2,
"count": 1,
"totalAverage": 531.2
},
{
"numberDay": 3,
"stringWeek": "Miércoles",
"date": "2022-10-25",
"total": 85.6,
"count": 1,
"totalAverage": 85.6
}
]
},
{
"mes": "Nov",
"count": 7,
"total": 2205.2,
"totalAverage": 1014.8,
"sales": [
{
"numberDay": 4,
"stringWeek": "Jueves",
"date": "2022-11-02",
"total": 526.8,
"count": 3,
"totalAverage": 175.6
},
{
"numberDay": 6,
"stringWeek": "Sábado",
"date": "2022-11-25",
"total": 171.2,
"count": 2,
"totalAverage": 85.6
},
{
"numberDay": 7,
"stringWeek": "Domingo",
"date": "2022-11-12",
"total": 1507.2,
"count": 2,
"totalAverage": 753.6
}
]
},
{
"mes": "Dec",
"count": 33,
"total": 12587.6,
"totalAverage": 4074.5,
"sales": [
{
"numberDay": 3,
"stringWeek": "Miércoles",
"date": "2022-12-06",
"total": 850,
"count": 1,
"totalAverage": 850
},
{
"numberDay": 6,
"stringWeek": "Sábado",
"date": "2022-12-02",
"total": 8737.6,
"count": 25,
"totalAverage": 349.5
},
{
"numberDay": 7,
"stringWeek": "Domingo",
"date": "2022-12-10",
"total": 900,
"count": 1,
"totalAverage": 900
},
{
"numberDay": 1,
"stringWeek": "Lunes",
"date": "2022-12-04",
"total": 200,
"count": 1,
"totalAverage": 200
},
{
"numberDay": 2,
"stringWeek": "Martes",
"date": "2022-12-05",
"total": 500,
"count": 1,
"totalAverage": 500
},
{
"numberDay": 5,
"stringWeek": "Viernes",
"date": "2022-12-08",
"total": 250,
"count": 2,
"totalAverage": 125
},
{
"numberDay": 4,
"stringWeek": "Jueves",
"date": "2022-12-07",
"total": 250,
"count": 1,
"totalAverage": 250
},
{
"numberDay": 6,
"stringWeek": "Sábado",
"date": "2022-12-09",
"total": 900,
"count": 1,
"totalAverage": 900
}
]
},
{
"mes": "Sep",
"count": 7,
"total": 5596,
"totalAverage": 1442.8999999999999,
"sales": [
{
"numberDay": 2,
"stringWeek": "Martes",
"date": "2022-09-12",
"total": 5069.2,
"count": 4,
"totalAverage": 1267.3
},
{
"numberDay": 6,
"stringWeek": "Sábado",
"date": "2022-09-02",
"total": 526.8,
"count": 3,
"totalAverage": 175.6
}
]
}
]
},
{
"_id": 2021,
"monthsWithSales": 1,
"count": 2,
"total": 608,
"totalAverage": 608,
"sales": [
{
"mes": "Dec",
"count": 2,
"total": 608,
"totalAverage": 608,
"sales": [
{
"numberDay": 1,
"stringWeek": "Lunes",
"date": "2021-12-12",
"total": 171.2,
"count": 1,
"totalAverage": 171.2
},
{
"numberDay": 4,
"stringWeek": "Jueves",
"date": "2021-12-22",
"total": 436.8,
"count": 1,
"totalAverage": 436.8
}
]
}
]
}
]
It is correct, but as you can see at the end I make two groups to obtain the data grouped by year and then the sales array grouped by month.
Is there any way to improve this query without so much grouping?