how to get count of MongoDB documents in a given date range

Viewed 21

The below mentioned is the Collection used:

[
  {
    _id: 1,
    joinedAt:ISODate("2021-07-20T12:31:33.229+05:30"),
    firstName: "John",
    lastName: "King",
    salary: 5000,
    department: {
      "name": "HR"
    }
  },
  {
    _id: 2,
    joinedAt:ISODate("2021-08-20T12:31:33.229+05:30"),
    firstName: "Sachin",
    lastName: "T",
    salary: 8000,
    department: {
      "name": "Marketing"
    }
  },
  {
    _id: 3,
    joinedAt:ISODate("2021-06-20T12:31:33.229+05:30"),
    firstName: "James",
    lastName: "Bond",
    salary: 7500,
    department: {
      "name": "Marketing"
    }
  },
  {
    _id: 4,
    joinedAt:ISODate("2021-05-20T12:31:33.229+05:30"),
    firstName: "Rosy",
    lastName: "Brown",
    salary: 5000,
    department: {
      "name": "HR"
    }
  },
  {
    _id: 5,
    joinedAt:ISODate("2021-07-26T12:31:33.229+05:30"),
    firstName: "Kapil",
    lastName: "D",
    salary: 4500,
    department: {
      "name": "HR"
    }
  },
  {
    _id: 6,
    joinedAt:ISODate("2021-07-20T12:31:33.229+05:30"),
    firstName: "Amitabh",
    lastName: "B",
    salary: 7000,
    department: {
      "name": "Marketing"
    }
  }
]

I used the following query:

db.collections.aggregate([ 
    { 
        $match:
     { createdAt: 
            {
                $gte: ISODate("2021-01-01T17:06:02.713+05:30"),
                $lte: ISODate("2021-12-31T17:06:02.713+05:30")
            }
      }
    },
    {$sort: {createdAt: 1}},
    { $group:{ _id:{department:'$department.name'}, totalEmployees: 
        {$sum:1},firstEmployee: {$first: "$firstName"} }
    }])

Using this query I can get the count of employees in each department and the first name of the employee who joined first in each department. I would also like to get the count of employees joined in each department in a particular month(eg: employees joined HR department in September). What are the changes to be made in this query?

0 Answers
Related