How to get a previous value after a mongo unwind

Question

Is it possible to access the previous document after an $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.

Collections

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"
}

Desired output

  {
    _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,
    },

  },

Answer 1

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.