$unwind and then $group them together in the new (cleaned up) object?Using this Mongo aggregate: mongoplayground.net/p/IsM-h9DnhjJ I would like to get an average of all result stats and scores within a page, then an average of all the pages stats for a site.
I have three collections that are all related. Site > Page > Result (page result)
// sites
{
_id: "si-site-a",
teamId: "te-team-1",
name: "Site a"
}
// pages
{
_id: "pa-page-a",
teamId: "te-team-1",
siteId: "si-site-a",
name: "Page A",
status: "ok"
},
{
_id: "pa-page-b",
teamId: "te-team-1",
siteId: "si-site-a",
name: "Page B",
status: "ok"
}
// results
{
_id: "re-result-1",
pageId: "pa-page-a",
siteId: "si-site-a",
results: {
scores: {
performance: 0.75,
accessibility: 0.98,
"best-practices": 0.93,
seo: 1,
pwa: 0.36,
ecoscore: 0.94
},
stats: {
transferSize: 876995,
co2grams: 0.11,
pageLoadTime: 1722.284
}
},
region: "gb1",
timestamp: ISODate("2022-02-05T16:38:35.454Z"),
status: "ok"
}
{
_id: 'a',
name: 'Site a',
pages: [
{
_id: 'aa',
name: 'Page a',
status: 'ok',
resultAverages: {
ecoscore: 0.89,
transferSize: 556081,
co2grams: 0.19,
pageLoadTime: 1011.7159999999999,
},
},
],
pageAverages: {
ecoscore: 0.89,
transferSize: 556081,
co2grams: 0.19,
pageLoadTime: 1011.7159999999999,
},
comparedTo: {
ecoscore: 0.89,
transferSize: 556081,
co2grams: 0.19,
pageLoadTime: 1011.7159999999999,
},
percentageChange: {
ecoscore: 0,
transferSize: 0,
co2grams: -0.19,
pageLoadTime: 0.24,
},
},
You are actually on the right track of using sub-pipeline. You can compute the avg within result in the sub-pipeline first. Then store them inside individual result. After the sub-pipeline, do another $avg to compute the avg for the previous avg inside result to get the avg for pages.
db.sites.aggregate([
{
$lookup: {
from: "pages",
localField: "_id",
foreignField: "siteId",
as: "pages",
pipeline: [
{
$lookup: {
from: "result",
localField: "_id",
foreignField: "pageId",
as: "results",
pipeline: [
{
$match: {
timestamp: {
$gte: ISODate("2022-01-01"),
$lte: ISODate("2022-12-01")
},
status: "ok"
}
}
]
}
},
{
$project: {
_id: 1,
name: 1,
status: 1,
resultAverages: {
ecoscore: {
$avg: "$results.results.stats.ecosore"
},
transferSize: {
$avg: "$results.results.stats.transferSize"
},
co2grams: {
$avg: "$results.results.stats.co2grams"
},
pageLoadTime: {
$avg: "$results.results.stats.pageLoadTime"
}
}
}
}
]
}
},
{
"$addFields": {
pageAverages: {
ecoscore: {
$avg: "$pages.resultAverages.ecosore"
},
transferSize: {
$avg: "$pages.resultAverages.transferSize"
},
co2grams: {
$avg: "$pages.resultAverages.co2grams"
},
pageLoadTime: {
$avg: "$pages.resultAverages.pageLoadTime"
}
}
}
}
])
Here is the Mongo playground for your reference.