How to add Bucket Sort to Query Aggregation

Question

I have a ElasticSearch Query that is working well (curl), is my first Query,

First I am filtering by Organization (Multitenancy), then group by Customer, Finally sum the amount of the sales but I only want to have the 3 best customers.

My question is.. How to build the aggregation with the AggregationBuilders to get "bucket_sort" statement. I got the sales grouping by customer with Java API.

Elastic Query is:

 curl -X POST 'http://localhost:9200/sales/sale/_search?pretty'  -H 'Content-Type: application/json' -d '
     {
         "aggs": {

     "filtered": {
       "filter": {
         "bool": {
           "must": [
             {
               "term": {
                 "organization_id": "15"
               }
             }
           ]
         }
       },
       "aggs": {
               "by_customer": {
                 "terms": {
                   "field": "customer_id"
                 },
                  "aggs": {
                      "sum_total" : {
                          "sum": {
                              "field": "amount"
                          }
                      },
                      "total_total_sort": {
                           "bucket_sort": {
                               "sort": [
                                 {"sum_total": {"order": "desc"}}
                               ],
                               "size": 3
                           }
                       }

                  }
               }
           }
     }
 }
 }'

My Java Code:

@Test
public void queryBestCustomers() throws UnknownHostException {
    Client client = Query.client();
    AggregationBuilder sum = AggregationBuilders.sum("sum_total").field("amount");
    AggregationBuilder groupBy = AggregationBuilders.terms("by_customer").field("customer_id").subAggregation(sum);
    AggregationBuilder aggregation =
            AggregationBuilders
                    .filters("filtered",
                            new FiltersAggregator.KeyedFilter("must", QueryBuilders.termQuery("organization_id", "15"))).subAggregation(groupBy);

    SearchRequestBuilder requestBuilder = client.prepareSearch("sales")
            .setTypes("sale")
            .addAggregation(aggregation);
    SearchResponse response = requestBuilder.execute().actionGet();
}

Answer 1

As another answer mentioned, "order" does work for your use case.

However there are other use cases where one may want to use bucket_sort. For example if someone wanted to page through the aggregation buckets.

As bucket_sort is a pipeline aggregation you cannot use the AggregationBuilders to instantiate it. Instead you'll need to use the PipelineAggregatorBuilders.

You can read more information about the bucket sort/pipeline aggregation here.

The ".from(50)" in the following code is an example of how you can page through the buckets. This causes the items in the bucket to start from item 50 if applicable. Not including "from" is the equivalent of ".from(0)"

BucketSortPipelineAggregationBuilder paging = PipelineAggregatorBuilders.bucketSort(
                    "paging", List.of(new FieldSortBuilder("sum_total").order(SortOrder.DESC))).from(50).size(10);
AggregationBuilders.terms("by_customer").field("customer_id").subAggregation(sum).subAggregation(paging);

Answer 2

I hope I got your question right. Try adding "order" to your groupBy agg:

AggregationBuilder groupBy = AggregationBuilders.terms("by_customer").field("customer_id").subAggregation(sum).order(Terms.Order.aggregation("sum_total", false));

One more thing, if you want the top 3 clients than your .size(3) should be set on groupBy agg as well and not on sorting. like that:
AggregationBuilder groupBy = AggregationBuilders.terms("by_customer").field("customer_id").subAggregation(sum).order(Terms.Order.aggregation("sum_total", false)).size(3);