ElasticSearch Order after 2 term aggregation

Question

Suppose that I have the following documents in ES, each has 3 fields: f1, f2 and score. I want to find all documents, group by f1, f2 and order by the group max score, in SQL I can simply do like this:

select f1,f2,score from table group by f1,f2 order by max(score)

What is the equivalent in elasticsearch? Nested term aggregation doesn't give correct order as the returned buckets of f2 terms are all nested in the same bucket of f1 terms.

this involves in multiple field aggregations, which is mentioned here that the current ES does not support. Still, I am not sure if there is any other way around. — boh

Vineeth Mohan Vineeth Mohan · Accepted Answer · 2015-11-04T11:29:36

The following aggregation should work

{
  "aggs": {
    "fileCombo": {
      "terms": {
        "script": "doc['field1'].value + doc['field2'].value",
        "order": {
          "maxScore": "desc"
        }
      },
      "aggs": {
        "maxScore": {
          "max": {
            "field": "score"
          }
        }
      }
    }
  }
}

First aggregate based on field1 and field2. Next order by the max field.

ElasticSearch Order after 2 term aggregation

1 Answers