Query top n value in kibana

Question

I learn KQL in kibana recently, I would like to show top n values based on id. In this table, each id could have different reach. I would like to see that each ID's top 2 maximum reach. I have this table as example:

id  reach
1   10       
1   12
1   3
3   7
1   13
3   12
3   90
4   12

How to choose top 2 based on reach. target:

id  reach
1   12
1   10
3   90
3   12
4   12

It can be done easily if I only want top 1 using Max Aggregate based on this source. source using max using max it only take top 1 value. How do we do it if we want to take more than 1 value ? Thank you

Answer 1

You'll need to use terms aggregation to get all unique IDs and then do a top-hits sub-aggregation to get TOP 2 per each ID:

{
  "aggs": {
    "ids": {
      "terms": {
        "field": "id",
        "size": 3
      },
      "aggs": {
        "top_reaches": {
          "top_hits": {
            "sort": [
              {
                "reach": {
                  "order": "desc"
                }
              }
            ],
            "_source": {
              "includes": ["reach" ]
            },
            "size": 2
          }
        }
      }
    }
  }
}

If you knew IDs in advance, you could use _msearch to issue a query per ID:

GET test-index/_msearch
{ }
{"size":2,"query" : {"term" : 1}, "sort": [{"reach": {"order": "desc"}}]}
{ }
{"size":2,"query" : {"term" : 2}, "sort": [{"reach": {"order": "desc"}}]}
{ }
{"size":2,"query" : {"term" : 3}, "sort": [{"reach": {"order": "desc"}}]}