Elasticsearch - group by and count

I have the following index and data in Elasticsearch that stores the booking records.

student_id | department | room_booked | start_time
peter      | A          | 1           | 2023-05-01 12:00:00
peter      | A          | 3           | 2023-05-02 12:00:00
tom        | A          | 2           | 2023-05-02 12:00:00
mary       | B          | 1           | 2023-05-02 12:00:00

I want to get the number of unique users in each department like below:

department A: 2
department B: 1

How to achieve that using aggregation in Elasticsearch?

You first need to aggregate by department using a terms aggregation and then add a cardinality sub-aggregation on the student_id field to get the unique count of students per department:

{
  "size": 0,
  "aggs": {
    "depts": {
      "terms": {
        "field": "department"
      },
      "aggs": {
        "count": {
          "cardinality": {
            "field": "student_id"
          }
        }
      }
    }
  }
}