How to sort a set of buckets by a field in the results

Question

I need to sort the buckets by a field "priority", defined as text, but I have no idea how to do it.

Do you mind to help me with this?

I've tried bucket_sort but ES gives an error about the type, same with sort and order.

this is the aggregations query

{
 "query": {
   [...]
  },
  "sort": [
    {
      "priority.keyword": {
        "order": "asc"
      }
    }
  ],
  "aggregations": {
    "by_family": {
      "terms": {
        "field": "familyId",
        "size": 25,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "_count": "desc"
          },
          {
            "_key": "asc"
          }
        ]
      },
      "aggregations": {
        "same_family": {
          "top_hits": {
            "from": 0,
            "size": 1,
            "version": false,
            "explain": false,
            "highlight": {
              "pre_tags": [
                "<search>"
              ],
              "post_tags": [
                "</search>"
              ],
              "fields": {
                "title*": {
                  "type": "unified"
                }
                }
              }
            }
          }
        }
      }
    }
  }

an example of results is:

{
  "responses" : [
    {
      "took" : 13117,
      "timed_out" : false,
      "_shards" : {
        "total" : 10,
        "successful" : 10,
        "skipped" : 0,
        "failed" : 0
      },
      "hits" : {
        "total" : 1754299,
        "max_score" : null,
        "hits" : [...]
      },
      "aggregations" : {
        "by_family" : {
          "doc_count_error_upper_bound" : 40,
          "sum_other_doc_count" : 1753462,
          "buckets" : [
            {
              "key" : 39031576,
              "doc_count" : 92,
              "same_family" : {
                "hits" : {
                  "total" : 92,
                  "max_score" : 10.636923,
                  "hits" : [
                    {
                      "_index" : "idx5-1554993721115",
                      "_type" : "_doc",
                      "_id" : "589403A-333506350",
                      "_score" : 10.636923,
                      "_source" : {
                        "number" : "589403A",
                        "suggest" : {
                          "input" : [
                            "589403A"
                          ]
                        },
                        "id" : "589403A-333506350",
                        "familyRepresentative" : 1,
                        "familyId" : 39031576,
                        "countryCode" : "NZ",
                        "number" : "589403",
                        "kind" : "A",
                        "family" : [ ],
                        "priority" : "20070425", <-------------
                        "created" : "2019-04-14",
                        "modified" : null,
                        "title" : [...],

I want to sort buckets aggregations (asc/desc) by field "priority" defined as text in the index

Val Val · Accepted Answer · 2019-05-15T14:49:14

You need to define another sub-aggregation (like max or min depending on how you want to sort) and then sort the parent terms aggregation by that metric. Remember that in your buckets by familyId, the documents might all have different values for the the priority field, so it doesn't make sense to sort buckets on a given document field, only on the aggregated value of that given field.

{
 "query": {
   [...]
  },
  "sort": [
    {
      "priority.keyword": {
        "order": "asc"
      }
    }
  ],
  "aggregations": {
    "by_family": {
      "terms": {
        "field": "familyId",
        "size": 25,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "max_priority": "desc"
          }
        ]
      },
      "aggregations": {
        "max_priority": {
          "max": {
              "script": "Long.parseLong(doc['priority.keyword'].value)"
          }  
        }
      }
    }
  }

How to sort a set of buckets by a field in the results

2 Answers