CODEWITHSUNDEEP
elasticsearch
Ihor
Ihor

Reputation: 297

Filter on latest document of each group in Elasticsearch

Let's assume that the following documents are indexed in Elasticsearch:

{student: "Chloe", date: "2016-10-27", grade: "A"}
{student: "Oliver", date: "2016-10-27", grade: "F"}
{student: "Chloe", date: "2016-10-26", grade: "B"}
{student: "Chloe", date: "2016-10-25", grade: "F"}
{student: "Oliver", date: "2016-10-25", grade: "A"}

I can use top hits aggregation to get a list of students with latest grade:

{student: "Chloe", date: "2016-10-27", grade: "A"}
{student: "Oliver", date: "2016-10-27", grade: "F"}

But how can I get a list of students whose latest grade is "F" (only student with name "Oliver" in this particular example)? For example, expected result is:

{student: "Oliver", date: "2016-10-27", grade: "F"}

Any ideas?

Upvotes: 2

Views: 909

Answers (1)

ChintanShah25
ChintanShah25

Reputation: 12672

You can do this with bucket selector aggregation(Only ES 2.x). I am basically comparing max date of each student with max date when they got F grade(filtering) and only retaining results where both dates are same. You can remove top hits aggregation if you want, it is just there to get that particular record where student failed.

{
  "size": 0,
  "aggs": {
    "group_by_students": {
      "terms": {
        "field": "student"
      },
      "aggs": {
        "only_f_grade_bucket": {
          "filter": {
            "term": {
              "grade": "F"
            }
          },
          "aggs": {
            "latest_date": {
              "max": {
                "field": "date"
              }
            },
            "top_hit":{
              "top_hits": {
                "size": 1
              }
            }
          }
        },
        "max_date": {
          "max": {
            "field": "date"
          }
        },
        "latest_failure": {
          "bucket_selector": {
            "buckets_path": {
              "failed_date": "only_f_grade_bucket.latest_date",
              "max_date": "max_date"
            },
            "script": "failed_date == max_date"
          }
        }
      }
    }
  }
}

Upvotes: 2

Related Questions