How to query data, and group them by month to feed line charts?-CodePudding

I'm facing some issue and I have no idea what keyword should I search for in the MongoDB docs.

So the problem is, I have a list of transactions record made by users right now, eg as below.

[
    {
        "_id": Some_Mongo_ID,
        "invoiceId": "ABCDEFG",
        "username": "randomUser"
        "amount": 80,
        "createdAt": "2022-04-18T06:59:07.836Z"
    },
    {
        "_id": Some_Mongo_ID,
        "invoiceId": "ABCDEFG",
        "username": "randomUser"
        "amount": 70,
        "createdAt": "2022-04-19T06:59:07.836Z"
    },
    {
        "_id": Some_Mongo_ID,
        "invoiceId": "ABCDEFG",
        "username": "randomUser"
        "amount": 55,
        "createdAt": "2022-05-18T06:59:07.836Z"
    },
        ...
]

In a Dashboard app that I'm working with right now, there's a line chart. To feed the line chart I will need to somehow make query/aggregation on the raw data of Transactions that I have, and group them into an array of objects, containing the months and total spend during that month.

[
    {
      "period": "2022-04",
      "totalSpending": 900
    },
    {
      "period": "2022-05",
      "totalSpending": 2000
    },
    {
      "period": "2022-06",
      "totalSpending": 367
    },
      ...
]

Is it technically possible to query/aggregate data that I have and make them group by months into an array as showed above?

Would be appreciated if any info is provided. Thanks

CodePudding user response：

Playground

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        month: { //Group by Month
          "$month": "$createdAt"
        },
        year: { //Group by Year
          "$year": "$createdAt"
        }
      },
      "totalSpending": { //Accumulate sum for the group
        $sum: "$amount"
      }
    }
  },
  {
    "$project": { //You can ignore this if the group format is ok
      "totalSpending": 1,
      "period": { //To form the expected string
        "$concat": [
          {
            $toString: "$_id.year"
          },
          "-",
          {
            $toString: "$_id.month"
          }
        ]
      },
      "_id": 0
    }
  }
])