How can I find out how many products were sold in each month?

Admin 1
Viewed 70

I have a collection with products with structure like this:

{   _id: 01,
    user_id: 10,
    line_items: [
      { 
        _id: 2,
        quantity: 2,
      },
      { 
        _id: 3,
        quantity:  1,
      }
    ],
    purchase_date: 2021-02-05T21:00:00.000+00:00
  }

How can I find out how many products were sold in each month?

aggregation-framework mongodb-query mongodb
2 Answers

Option 1 The easiest and faster is this:

 db.collection.aggregate([
 {
   $group: {
     _id: {
       "$substr": [
         "$purchase_date",
         0,
         7
       ]
    },
     count: {
       $sum: 1
     }
   }
 }
])

Explained:

Group by the first 7 characters that include year and month: "2021-12" and count the products.

playground1

Option 2: Convert string to date/month:

db.collection.aggregate([
{
  $group: {
    _id: {
      $month: {
        $dateFromString: {
          dateString: "$purchase_date"
        }
      }
     },
     count: {
       $sum: 1
     }
   }
  }
 ])

Explained:

Convert the string to month and group

playground2

user_104150470

To find out how many line items were sold each month, you need to run an aggregation where the pipeline consists of a $group stage. The group by key will be the month value returned by the $month operator on the purchase_date field. The count will consist of the $sum operator on another $sum of the array of quantities returned by the expression "$line_items.quantity" which essentially is interpreted as in the above document

{ $sum: [2, 1] } => 3

So your overall pipeline follows:

db.collection.aggregate([
  { $group: {
      _id: {
        "$month": "$purchase_date"
      },
      total: {
        $sum: {
          $sum: "$line_items.quantity"
        }
      }
  } }
])

Mongo Playground

user_1220050
Related