Elasticsearch query to group on different parameter and max of different parameter

Elastic Stack Elasticsearch
1

I want to write an Elasticsearch query to group by document on an attribute (say attr1), get only the top 10 result of this group by sorted by another attibute (say attr2) and in this result of 10 documents I need to find the max of an attribute (say attr3).

In sql, I would have written the same like this -

select max(attr3) from 
 (select top 10 * from 
    (select sum(xyz) as attr2, count(abc) as attr3 from sometable group by attr1 )  
 order by attr2 desc);

Could someone help me an analogous query in elasticsearch for it?

2

Have a look at aggregations. The terms aggregation is like group by. There
is also a max and average aggregation.

3

Thanks @nik9000 for your reply.

I found that if I perform a nested aggregation followed by pipeline aggregation then I was able to mimic the above stated SQL query. Following is the query payload I built -

POST /sometable/_search
{
   "from":0,
   "size":0,
   "query":{
      "match_all": {}
   },
   "aggs": {
     "group_by_attr1" : {
       "terms": {
         "field": "attr1",
         "order": {
          "attr2" : "desc" 
        },
        "size": 10
       },
       "aggs": {
         "attr2": {
           "sum": {
             "field": "xyz"
           }
         },
         "attr3" : {
           "value_count": {
             "field": "abc"
           }
         }
       }
     },
    "max_attr3": {
      "max_bucket": {
          "buckets_path": "group_by_attr1>attr3"
      }
    }
   }
}

I believe it is fine here. Do you have any comments/suggestions on my query?

1 Like