How to calculate the count of users each month based on the previous count with MongoDB query?-CodePudding

I need to calculate the accumulated count of users based on the count value from the previous month.

The results array should look like this:

RESULTS  [
  { count: 1, accumulatedCount: 1, month: 12, year: 2021, verified: true },
  { count: 3, accumulatedCount: 4, month: 1, year: 2022, verified: true },
  { count: 3, accumulatedCount: 7, month: 2, year: 2022, verified: true },
  { count: 1, accumulatedCount: 8, month: 3, year: 2022, verified: true },
]

Right now, my aggregation pipeline looks like this:

const results = await this.accountModel.aggregate([
      {
        $match: {
          ...match,
          createdAt: {
            // $gte: range.from,
            $lte: range.to,
          },
        },
      },
      { $unwind: '$organizations' },
      {
        $group: {
          _id: {
            month: { $month: '$createdAt' },
            year: { $year: '$createdAt' },
            verified: '$organizations.verified',
          },
          count: { $sum: 1 },
        },
      },
      {
        $project: {
          _id: 0,
          month: '$_id.month',
          year: '$_id.year',
          count: 1,
          verified: '$_id.verified',
        },
      },
    ]);

and it returns this:

RESULTS  [
  { count: 1, month: 10, year: 2022, verified: true },
  { count: 4, month: 7, year: 2022, verified: true },
  { count: 3, month: 2, year: 2022, verified: true },
  { count: 1, month: 3, year: 2022, verified: true },
  { count: 1, month: 12, year: 2021, verified: true },
  { count: 2, month: 1, year: 2022, verified: true },
  { count: 1, month: 8, year: 2022, verified: true }
]

Basically, I would need to reduce the count based on the prev and current value.

I couldn't find any suitable options in the MongoDB docs.

I use "mongodb": "3.6.3".

CodePudding user response：

This is one way of doing it:

db.collection.aggregate([
  {
    "$group": {
      "_id": null,
      "docs": {
        "$push": "$$ROOT"
      }
    }
  },
  {
    "$project": {
      docs: {
        "$reduce": {
          "input": "$docs",
          "initialValue": {
            accumulatedCount: 0,
            data: []
          },
          "in": {
            accumulatedCount: {
              "$sum": [
                "$$this.count",
                "$$value.accumulatedCount"
              ]
            },
            data: {
              "$concatArrays": [
                "$$value.data",
                [
                  {
                    "$mergeObjects": [
                      "$$this",
                      {
                        accumulatedCount: {
                          "$sum": [
                            "$$this.count",
                            "$$value.accumulatedCount"
                          ]
                        }
                      }
                    ]
                  }
                ]
              ]
            }
          }
        }
      }
    }
  },
  {
    "$unwind": "$docs.data"
  },
  {
    "$replaceRoot": {
      "newRoot": "$docs.data"
    }
  }
])

Group all documents, in an array. Loop over the array using $reduce, keep the running sum of counts, and the updated list of documents with accumulatedCount, in two variables. Finally, unwind the list and bring the docs to the root.

Playground link.

You can just add the new stages in your pipeline.