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,
}
]
]
}
CodePudding user response：
You can simply add another projection step:
db.collection.aggregate([
{
$project: {
last_seven_day: {
data: "$last_seven_day_arr",
avg: {
$avg: "$last_seven_day_arr.hits"
}
},
last_month: {
data: "$last_month_arr",
avg: {
$avg: "$last_month_arr.hits"
}
},
last_year: {
data: "$last_year_arr",
avg: {
$avg: "$last_year_arr.hits"
}
}
}
}
])
See how it works on the playground
CodePudding user response：
You can use $filter to filter the records first. Then use another $avg to compute the average in filtered result.
db.collection.aggregate([
{
"$match": {
userId: "6udg"
}
},
{
"$addFields": {
"last_seven_days": {
data: {
"$filter": {
"input": "$data",
"as": "d",
"cond": {
$lt: [
{
"$dateDiff": {
"startDate": "$$d.date",
"endDate": "$$NOW",
"unit": "day"
}
},
7
]
}
}
}
},
"last_month": {
data: {
"$filter": {
"input": "$data",
"as": "d",
"cond": {
$lt: [
{
"$dateDiff": {
"startDate": "$$d.date",
"endDate": "$$NOW",
"unit": "month"
}
},
1
]
}
}
}
},
"last_year": {
data: {
"$filter": {
"input": "$data",
"as": "d",
"cond": {
$lt: [
{
"$dateDiff": {
"startDate": "$$d.date",
"endDate": "$$NOW",
"unit": "year"
}
},
1
]
}
}
}
}
}
},
{
"$addFields": {
"last_seven_days": {
avg: {
$avg: "$last_seven_days.data.hits"
}
},
"last_month": {
avg: {
$avg: "$last_month.data.hits"
}
},
"last_year": {
avg: {
$avg: "$last_year.data.hits"
}
}
}
}
])