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?

CodePudding user response：

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

CodePudding user response：

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