What is the most efficient way to determine the monthly user count using a MongoDB query that takes into account the previous count?

Question

What is the most efficient way to determine the monthly user count using a MongoDB query that takes into account the previous count?

I am looking to calculate the total count of users based on the previous month's count data.

Here is an example of how the results array should be structured:

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 },
]

Currently, my aggregation pipeline is structured as follows:

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',
        },
      },
    ]);

The current output of the aggregation pipeline includes the following:

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

Essentially, I need to utilize a reduce function to aggregate the count values based on previous and current data.

I have not found appropriate options for this in the MongoDB documentation.

The version of "mongodb" package I am using is "3.6.3".

javascript mongodb mongoose aggregation-framework

Answer 1

Answer №1

Here is a method to achieve the desired outcome:

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

To summarize, group all documents into an array and iterate over them using $reduce, maintaining a count total and updated document list with accumulatedCount. Lastly, flatten the list and set the documents as the root.

Interactive demonstration here.

You can seamlessly integrate these new stages into your pipeline for execution.

Answer 2

Here is a method to achieve the desired outcome:

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

To summarize, group all documents into an array and iterate over them using $reduce, maintaining a count total and updated document list with accumulatedCount. Lastly, flatten the list and set the documents as the root.

Interactive demonstration here.

You can seamlessly integrate these new stages into your pipeline for execution.

What is the most efficient way to determine the monthly user count using a MongoDB query that takes into account the previous count?

Answer №1

Similar questions

Deleting a page reference from the page history stack in Angular 4

Odd behaviour when using JSON in CouchDB

Toggle between different socket.io servers for seamless connectivity

Guide to sending multiple forms from an HTML page to another PHP page

Is it seriously impossible to create a TTL index on a field that is already indexed?

Steps for creating a table with a filter similar to the one shown in the image below

Attempts to access the URL but receives no feedback

Why is my snapshot returning null, even though there are values in the Firebase Database?

Operating on Javascript Objects with Randomized Keys

State of loading getServerSideProps in Next.js

Discover the ultimate solution to disable JSHint error in the amazing Webstorm

Guide on including a particular field in an array of objects within a MongoDB projection

Changing the background color of the dropdown button in Vue Bootstrap: A step-by-step guide

What's the best way to animate the navigation on top of an image for movement?

Incorporate additional text to the downloads hyperlink using jquery

Ways to disable the ability to close a bootstrap modal by pressing the backspace key

Issues with Bootstrap Table Column Widths not Adjusting Properly

Can a Node.js dependent library be integrated into Cordova?

Is there a way to successfully integrate a JavaScript file that has been downloaded from `npm` or `yarn` into a web client or

When attempting to parse a JSON feed with jQuery and innerHTML, the data fails to display