CODEWITHSUNDEEP
elasticsearch
boh
boh

Reputation: 1547

ElasticSearch Order after 2 term aggregation

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.

Upvotes: 1

Views: 210

Answers (1)

Vineeth Mohan
Vineeth Mohan

Reputation: 19253

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.

Upvotes: 3

Related Questions