mongodb get earning current month and previous month from transactions

on

In many cases while building a report or dashboard we will be in situation to show the user data comparison like difference between current month and last month or current week with previous week. so here is the example to do that in mongo aggregation query.

To get the earning of the last 30 days and the previous 30 days from a MongoDB collection of transactions, you can use the MongoDB aggregation framework with a pipeline that performs the following steps:

  • Filter the transactions that occurred in the last 60 days by using the $match stage with a query that uses the $gte operator to match transactions with a timestamp greater than or equal to 60 days ago.
  • Group the filtered transactions by date using the $group stage with the $dateToString operator to convert the timestamp field to a string that represents the date without the time component. Then, use the $sum operator to calculate the total earnings for each date.
  • Sort the grouped results by date using the $sort stage in ascending order.
  • Use the $facet stage to split the results into two arrays: one for the last 30 days and another for the previous 30 days. For this, you can use two sub-pipelines with the $match stage to filter the results that fall within the desired date range.

Here’s an example pipeline that implements these steps:

<code class="">db.transactions.aggregate([
  // Match transactions in the last 60 days
  { $match: { timestamp: { $gte: new Date(Date.now() - 60 * 24 * 60 * 60 * 1000) } } },
  // Group by date and calculate total earnings
  {
    $group: {
      _id: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: "$timestamp"
        }
      },
      earnings: { $sum: "$amount" }
    }
  },
  // Sort by date in ascending order
  { $sort: { _id: 1 } },
  // Split results into two arrays: last 30 days and previous 30 days
  {
    $facet: {
      last30Days: [
        { $match: { _id: { $gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) } } }
      ],
      previous30Days: [
        { $match: { _id: { $lt: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000), $gte: new Date(Date.now() - 60 * 24 * 60 * 60 * 1000) } } }
      ]
    }
  }
])
</code>

This pipeline will return an array with two elements: the first element will contain the results for the last 30 days, and the second element will contain the results for the previous 30 days. Each element will be an array of objects with the _id field representing the date and the earnings field representing the total earnings for that date.

Posted in MongoDB by .

About Gowri

I am professional web developer with 8+ years experience. PHP, jQuery, WordPress, Angular and Ionic are my key skills in web development. I am working with strong enthusiastic team with spirit. We provide all web related solution like HTML/CSS development, Web graphic design and Logo.