# Top-hits on aggregations

**URL:** <https://discuss.elastic.co/t/top-hits-on-aggregations/274959>\
**Category:** Elasticsearch\
**Created:** [June 4, 2021, 8:20am UTC](https://discuss.elastic.co/t/top-hits-on-aggregations/274959 "2021-06-04T08:20:12Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![romathonat](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@romathonat](https://discuss.elastic.co/u/romathonat)\
**Post date:** [June 4, 2021, 8:20am UTC](https://discuss.elastic.co/t/top-hits-on-aggregations/274959/1 "2021-06-04T08:20:12Z")

</div>

Hello,  
I need to get the top-hits over an aggregation.  
In my use case I store SQL queries as documents (with different statistics like the time it tooks). Each querie is hashed, so that I can make an aggregation term on it.  
I then have "groups" of exact same sql queries, on which I can compute aggregations, like the average time each query took.

Now I want to get the top-k queries w.r.t mean time it tooks. How can I do so ?  
If i use a bucket\_sort, I know that elastic will sort buckets **after** having extracting them, and because the number of bucket is limited, I do not have the guarantee to have top-k on this aggregation metric.

This is the code I came with so far:

```auto
GET /sql_requests_parsed/_search/
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "exists": {"field": "request"}
        }
      ]
    }
  },
  "aggs": {
    "same_requests": {
      "terms": {
        "field": "hashed_request",
        "size": 2000
      }, 
      "aggs": {
        "request": {
          "top_hits": {
            "size": 1
          }
        },
        "mean_call": {
          "avg": {
            "field": "callDuration"
          }
        },
        "sorting": {
          "bucket_sort": {
            "sort": [
              {"mean_call": {"order": "desc"}} 
            ]
          }
        }
      }
    }
  }
}

```

NB: I also tried "collapse", which seemed to fit my usecase at first glance, but I can't aggregate on the resulting inner hits.

---

<div class="post-metadata">

**Author:** ![romathonat](https://avatars.discourse-cdn.com/v4/letter/r/71c47a/32.png) [@romathonat](https://discuss.elastic.co/u/romathonat)\
**Post date:** [June 11, 2021, 7:25am UTC](https://discuss.elastic.co/t/top-hits-on-aggregations/274959/2 "2021-06-11T07:25:19Z")

</div>

I cannot find a good solution to this, if you know something I would be glad to hear it.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 9, 2021, 7:25am UTC](https://discuss.elastic.co/t/top-hits-on-aggregations/274959/3 "2021-07-09T07:25:30Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.