I would like aggregate this data, i need count the objects by month, like census. For each item i have arrival date and departure date, this item have sum 1 on months for this range. How can i do this?

DATA:

[{
    _id:1
    history:
    [
       {_id:1, date:2021-03-01, type:"Departure"},     
       {_id:2, date:2021-01-01, type:"Arrival"}
    ]
},
{
    _id:2
    history:
    [
       {_id:1, date:2021-07-03, type:"Departure"}, 
       {_id:2, date:2021-02-10, type:"Arrival"}
    ]
},
{
    _id:3
    history:
    [
       {_id:1, date:2021-08-22, type:"Departure"}, 
       {_id:2, date:2021-11-5, type:"Arrival"}
    ]
}]

RESULT:

    {month:1, year:2021, counter:1},
    {month:2, year:2021, counter:2},
    {month:3, year:2021, counter:2},
    {month:3, year:2021, counter:1},
    {month:4, year:2021, counter:1},
    {month:5, year:2021, counter:1},
    {month:6, year:2021, counter:1},
    {month:7, year:2021, counter:0},
    {month:8, year:2021, counter:1},
    {month:9, year:2021, counter:1},
    {month:10, year:2021, counter:1},
    {month:11, year:2021, counter:1},
    {month:12, year:2021, counter:0}
]

CodePudding user response：

I change your data _id=3, since departure date should be larger than arrival date.

• $match
• $set
• $set
• $set
• $project
• $unwind
• $group
• $project
• $sort

db.collection.aggregate([
  {
    "$match": {}
  },
  {
    "$set": {
      arrival: {
        $dateFromString: {
          dateString: {
            $arrayElemAt: [ "$history.date", 1 ]
          }
        }
      },
      departure: {
        $dateFromString: {
          dateString: {
            $arrayElemAt: [ "$history.date", 0 ]
          }
        }
      }
    }
  },
  {
    "$set": {
      monthDiff: {
        $dateDiff: {
          startDate: "$arrival",
          endDate: "$departure",
          unit: "month"
        }
      }
    }
  },
  {
    "$set": {
      historyRange: {
        "$map": {
          "input": { $range: [ 0, { $add: [ "$monthDiff", 1 ] }, 1 ] },
          "as": "m",
          "in": {
            item: {
              $dateAdd: {
                startDate: "$arrival",
                unit: "month",
                amount: "$$m"
              }
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      historyRange: {
        "$map": {
          "input": "$historyRange",
          "as": "h",
          "in": {
            year: { $year: "$$h.item" },
            month: { $month: "$$h.item" }
          }
        }
      }
    }
  },
  {
    "$unwind": "$historyRange"
  },
  {
    "$group": {
      "_id": "$historyRange",
      "counter": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      _id: 0,
      counter: 1,
      month: "$_id.month",
      year: "$_id.year"
    }
  },
  {
    "$sort": { month: 1 }
  }
])

mongoplayground