Here is my data:

{
"_id": ObjectId("6213ba90a013b7c5f1232e1f"),
"name": "name1",
"surname": "surname1",
"newArray": {
      "buyDate": ISODate("1975-11-04T13:14:15Z"),
      "carCount": 3,
      "name": "name1",
      "surname": "surname1"
    }
},
{
"_id": ObjectId("6213ba90a013b7c5f1232e2f"),
"name": "name2",
"surname": "surname2",
"newArray": {
      "buyDate": ISODate("1978-12-07T15:30:15Z"),
      "carCount": 7,
      "name": "name2",
      "surname": "surname2"
    }
},
{
"_id": ObjectId("6213ba90a013b7c5f1232e3f"),
"name": "name3",
"surname": "surname3",
"newArray": {
      "buyDate": ISODate("1983-11-12T18:40:15Z"),
      "carCount": 4,
      "name": "name3",
      "surname": "surname3"
    }
}

I want to calculate car counts by their buying month. First I want to find their buy month for all "newArray" and then add to all "newArray". I mean:

{
"_id": ObjectId("6213ba90a013b7c5f1232e1f"),
"name": "name1",
"surname": "surname1",
"newArray": {
      "buyDate": ISODate("1975-11-04T13:14:15Z"),
      "carCount": 3,
      "name": "name1",
      "surname": "surname1",
      "buyMonth": 11
    }
},
{
"_id": ObjectId("6213ba90a013b7c5f1232e2f"),
"name": "name2",
"surname": "surname2",
"newArray": {
      "buyDate": ISODate("1978-12-07T15:30:15Z"),
      "carCount": 7,
      "name": "name2",
      "surname": "surname2",
      "buyMonth": 12
    }
},
{
"_id": ObjectId("6213ba90a013b7c5f1232e3f"),
"name": "name3",
"surname": "surname3",
"newArray": {
      "buyDate": ISODate("1983-11-12T18:40:15Z"),
      "carCount": 4,
      "name": "name3",
      "surname": "surname3",
      "buyMonth": 11
    }
}

Lastly I will calculate car count by their buying month. I've tried but I do not get any result with this:

db.collection.aggregate([
  {
    $set: {
      "newArray.buyMonth": "$buyDate.month"
    }
  },
  {
    "$group": {
      "_id": "$buyMonth",
      "carCountByMonth": {
        "$count": "newArray"
      }
    }
  }
])

How can I get what I want? Thanks in advance.

CodePudding user response：

1. To get month from date, use $month operator.
2. To get newArray.buyDate, use newArray.buyDate but not buyDate.
3. $group by newArray.buyMonth but not buyMonth.
4. Use $sum to calculate sum of all newArray.carCount instead of $count.

db.collection.aggregate([
  {
    $set: {
      "newArray.buyMonth": {
        $month: "$newArray.buyDate"
      }
    }
  },
  {
    "$group": {
      "_id": "$newArray.buyMonth",
      "carCountByMonth": {
        "$sum": "$newArray.carCount"
      }
    }
  }
])

Sample Mongo Playground