How to write the query code for the given mongo query

Viewed 13

I have used the below mentioned collection:

[
  {
    _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 have used the following query to get the details of first employee from each department who joined first in respective department and count of total employees in each department using aggregate method for a given year.

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

How to write mongo query code for the above mentioned query? I am able to write the mongo query code individually for each operation like:

Query query = new Query();
query.addCriteria(Criteria.where("joinedAt").lt(ISODate("2021-12-31T17:06:02.713+05:30")).gt(ISODate("2021-01-01T17:06:02.713+05:30")));
List<User> users = mongoTemplate.find(query,User.class);
0 Answers
Related