so here’s my collection. This consists of users and their data
{
userId: 6udg,
data: [
{
date: 22-09-2022
hits: 98
},
{
date: 23-09-2022
hits: 88
},
{
date: 24-09-2022
hits: 100
},
{
date: 24-11-2022
hits: 145
},
{
date: 25-11-2022
hits: 75
}
]
},
{
userId: 7tu5,
data: [
{
date: 22-09-2022
hits: 98
},
{
date: 23-09-2022
hits: 88
},
{
date: 24-09-2022
hits: 100
},
{
date: 24-11-2022
hits: 18
},
{
date: 25-11-2022
hits: 65
}
]
}
Here’s how I’m creating an aggregate with objects for hits filtered by week, month and year. First I match the user whose data we want to fetch. Then I use projection to get the custom fields that I want.
Users.aggregate([
{
$match: {
userId: req.params.userId
}
},
{
$project: {
_id: 0,
last_seven_days: {
$filter: {
input: "$data",
as: "index",
cond: {
$and: [
{
$gte: [
"$$index.date",
new Date(moment().utc().startOf("week"))
]
},
{
$lte: [
"$$index.date",
new Date(moment().utc().endOf("week"))
]
}
]
}
},
},
last_month: {
$filter: {
input: "$data",
as: "index",
cond: {
$and: [
{
$gte: [
"$$index.date",
new Date(moment().utc().startOf("month"))
]
},
{
$lte: [
"$$index.date",
new Date(moment().utc().endOf("month"))
]
}
]
}
}
},
last_year: {
$filter: {
input: "$data",
as: "index",
cond: {
$and: [
{
$gte: [
"$$index.date",
new Date(moment().utc().startOf("year"))
]
},
{
$lte: [
"$$index.date",
new Date(moment().utc().endOf("month"))
]
}
]
}
}
}
}
}
])
what I want to do is add a key called ‘average’ in each last_seven_days , last_month, and last_year – containing the average hits for week, month and year respectively
Expected output:
{
userId: 6udg
last_seven_day:[
avg: <avg>
data:[
{
date: 24-11-2022,
hits: 145,
},
{
date: 25-11-2022,
hits: 75,
}
]
]
}
2
Answers
You can simply add another projection step:
See how it works on the playground
You can use
$filter
to filter the records first. Then use another$avg
to compute the average in filtered result.Mongo Playground