Sorting aggregation results by sub aggregation in ascending order

user_11423881 0
Viewed 412

I am trying to sort the values of my aggregated results, in ascending order, to another aggregation result.

Code:

"aggregation":[
"agg_max_saving_percent"=> [
                    "terms"=> [
                        "field"=> 'key',
                        'size' => 60,
                        'order' => [ 'maximma' => 'desc' ]
                    ],

                    "aggs"=> [
                        "offers"=> [
                            "nested"=> [
                                "path"=> "offers"
                            ],
                            "aggs"=> [
                                "found_savper"=> [
                                    "max"=> [
                                        "field"=> "offers.savper"
                                    ]
                                ]
                            ],

                        ]
                    ],
                    "aggs"=> [  "maximma" => ["max" => ['field' => "sum_score"]]],

                ]
]

Mapping:

"offers": {
 "type": "nested",
            "properties": {
             "savper": {"type": "long"}
}
}

"sum_score": {"type": "long"},

At the moment, I am only getting the results of 'MAXIMA', with which I am sorting and not the results of 'found_savper'

Although, I need the results of found_savper and want the bucket to be sorted by maxima desc. Any suggestions?

elasticsearch
1 Answers

You would need to make use of Bucket Sort Aggregation for the use case you are looking for.

Below are the sample documents, the aggregation query I've implemented and the response:

Documents:

POST myaggregation/_doc/1
{
  "key": "1001",
  "offers":[
    {
      "savper": 1000
    },
    {
      "savper": 2000
    }
  ],
  "sum_score": 1
}

POST myaggregation/_doc/2
{
  "key": "1001",
  "offers":[
    {
      "savper": 3000
    },
    {
      "savper": 4000
    }
  ],
  "sum_score": 2
}

POST myaggregation/_doc/3
{
  "key": "1002",
  "offers":[
    {
      "savper": 1000
    },
    {
      "savper": 2000
    }
  ],
  "sum_score": 2
}

POST myaggregation/_doc/4
{
  "key": "1002",
  "offers":[
    {
      "savper": 3000
    },
    {
      "savper": 4000
    }
  ],
  "sum_score": 4
}

Aggregation Query:

POST myaggregation/_search
{
  "size": 0,
  "aggs": {
    "myaggs": {
      "terms": {
        "field": "key",
        "size": 10
      },
      "aggs": {
        "mynested": {
          "nested": {
            "path": "offers"
          },
          "aggs": {
            "found_savper": {
              "max": {
                "field": "offers.savper"
              }
            }
          }
        },
        "maxima":{
          "max": {
            "field": "sum_score"
          }
        },
        "sort": {
          "bucket_sort": {
            "sort": [
              { "maxima": { "order": "desc" }}
            ]
          }
        }
      }
    }
  }
}

Note that the sorting order on maxima aggregation is desc. You can change it to asc depending on your use case.

Aggregation Response:

{
  "took" : 3,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "myaggs" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "1002",
          "doc_count" : 2,
          "maxima" : {
            "value" : 4.0
          },
          "mynested" : {
            "doc_count" : 4,
            "found_savper" : {
              "value" : 4000.0
            }
          }
        },
        {
          "key" : "1001",
          "doc_count" : 2,
          "maxima" : {
            "value" : 2.0
          },
          "mynested" : {
            "doc_count" : 4,
            "found_savper" : {
              "value" : 4000.0
            }
          }
        }
      ]
    }
  }
}

If you remove the bucket sort aggregation logic, you would observe that the aggregation would be sorted by key.

Hope this helps!

user_38383280
Related