Sorting after aggregation in Elasticsearch

Question

I have docs with this structure:

{
    FIELD1:string,
    FIELD2:
        [ {SUBFIELD:number}, {SUBFIELD:number}...]
}

I want to sort on the result of the sum of numbers in FIELD2.SUBFIELDs:

GET myindex/_search
{
  "size":0,
  "aggs": {
    "a1": {
      "terms": { 
        "field": "FIELD1",
        "size":0
      },
      "aggs":{
        "a2":{
          "sum":{
            "field":"FIELD2.SUBFIELD"
          }
        }
      }
    }
  }
}

If I do this I obtain buckets not sorted, but I want buckets sorted by "a2" value. How I can do this? Thank you!

score 72 · Accepted Answer · answered Jun 10 '15 at 14:57

72

You almost had it. You just need to add an order property to your a1 terms aggregations, like this:

GET myindex/_search
{
  "size":0,
  "aggs": {
    "a1": {
      "terms": { 
        "field": "FIELD1",
        "size":0,
        "order": {"a2": "desc"}      <--- add this
      },
      "aggs":{
        "a2":{
          "sum":{
            "field":"FIELD2.SUBFIELD"
          }
        }
      }
    }
  }
}

answered Jun 10 '15 at 14:57

Val

207,596
13
358
360

1

@ErdalG. the documentation or ordering terms aggregations is [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-order) – Val Sep 01 '16 at 19:56
2

@Val this is not working when `a2` is a `scripted_metric` aggregation. I used it for my cases that I have to use `scripted_metric` aggregation. I got this error `"reason": { "type": "aggregation_execution_exception", "reason": "Invalid terms aggregation order path [totalViews]. Terms buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end." }` – Saeed Zhiany Oct 07 '16 at 12:14
1

@saeedzhiany Correct, there's an [open PR](https://github.com/elastic/elasticsearch/pull/15718) and an [open issue](https://github.com/elastic/elasticsearch/issues/8486) about this. – Val Oct 07 '16 at 12:20
@Val, is there any alternate for this sort? I really need it – Saeed Zhiany Oct 07 '16 at 12:24
@saeedzhiany not that I know of. Usually, people sort on the client side. Feel free to ask a new question, maybe someone has an idea. – Val Oct 07 '16 at 12:26

score 6 · Answer 2 · answered Jan 14 '20 at 05:26

Brilliant from Val https://stackoverflow.com/users/4604579/val

Basically the same thing, but here's what worked for me to find the largest "size" for each "name", and to show the top 25 largest:

{
  "size": 0,
  "aggs": {
    "agg1": {
      "terms": {
        "field": "name.keyword",
        "order": {
          "agg2": "desc"
        },
        "size": 25
      },
      "aggs": {
        "agg2": {
          "max": {
            "field": "size"
          }
        }
      }
    }
  }
}

Sorting after aggregation in Elasticsearch

2 Answers2

Linked