How to visualize max aggregation inside terms aggregation in Kibana?

[cr_168328]

I have an index exams which contains data related to students who passed some exams.

A student can attempt an exam multiple times. Hence there may be more than one document in the index for a particular student and exam, with a different passedOn date.

I want to create a Kibana Visualization of the count of unique exam completions, ignoring the duplicate values of student-exam pair.

The following query gives the latest date of passing each exam by each student:

GET exams/_search
{
  "size": 0, 
  "_source": false,
  "aggs": {
    "student": {
      "terms": {
        "field": "studentId",
        "size": 20000
      },
      "aggs": {
        "exam": {
          "terms": {
            "field": "examId",
            "size": 1000
          },
          "aggs": {
            "latest": {
              "max": {
                "field": "passedOn"
              }
            }
          }
        }
      }
    }
  }
}

How can I visualize the unique count of student-exam pair, either using the latest passedOn date or using runtime fields or scripted fields or some other way?

[Marco_Liberati]

Hi @cr_168328 ,

what if instead of focusing on the last value of an exam, you just count the unique_count of passed exams per student?

I mean using the unique count Lens operation, with a filter on passed: true in it, and a break down per student.
Would that work?

[cr_168328]

Hi @Marco_Liberati ,

Thanks for the reply.

What I want exactly is not a tabular data, but a count of unique student-exam pairs.

For eg., if I have the following data:

Student | Exam | Passed On
s1 | e1 | 2022/01/01
s1 | e1 | 2022/01/02
s1 | e1 | 2022/01/03
s1 | e2 | 2022/01/03
s1 | e2 | 2022/01/04
s1 | e2 | 2022/01/05
s1 | e3 | 2022/01/03
s1 | e3 | 2022/01/04
s2 | e1 | 2022/01/03
s2 | e1 | 2022/01/04
s2 | e1 | 2022/01/05
s2 | e2 | 2022/01/03
s2 | e2 | 2022/01/04

The required result is the count of unique student-exam pairs = 5.

[Marco_Liberati]

So there's no actual information about passing of an exam, other than the latest PassedOn date?
There's no result field?

[cr_168328]

No. There is no passed field. Actually, the index contains details of only students who have passed each exam