I am currently using Elasticsearch 8.15. I am in a bit of a conundrum trying to achieve outer and inner bucket sorting for strings (sorted asc or desc alphabetically). My index, hazard, looks a little something like this:

{
  "hazards" : {
    "mappings" : {
      "properties" : {
        "articleScoreValue" : {
          "type" : "long"
        },
        "bestTime" : {
          "type" : "date
        "clusterId" : {
          "type" : "long"
        },
        "hazardType" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "id" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "likelihood" : {
          "type" : "long"
        }, 
        "publishedAt" : {
          "type" : "date"
        },
      }
    }
  }
}

I am trying to achieve a terms aggregation on my hazards, where the field is clusterId and the size ~ 10,000. Hazards with the same clusterId are hazards that are very similar to each-other. My requirements for the aggregation are to paginate and sort. I am currently using a bucket_sort to paginate my aggregations as well as sort them. The 4 possible parameters of the sort are of long(articleScoreValue), long(likelihood), date(publishedAt), and text(hazardType.keyword, E.X: “WILDFIRE”, “ACCIDENT”, “CYCLONE”) types.

After the elastic search responds, I am processing the clusters so that the very first entry in the bucket will become the “main” hazard, and the following hazards in the bucket will become the “main” hazard’s, “clustered” hazards. The way I am deciding on how to choose the “main” hazard is by also sorting inside the buckets. That way the most relevant hazard depending on the sort criteria will be come the “main” hazard and be the first element in the bucket.

For the 3 metric sort types of long, long, and date, I have achieved this with min/max metric aggregations, which the bucket_sort uses to sort the buckets according. However I am having an issue when trying to achieve this with hazardType.keyword. Essentially, I want each bucket to find the hazard with the highest or lowest alphabetical hazardType, then use that hazard’s highest or lowest hazardType as the bucket’s sorting point. Then, other buckets could compare with their sorting points lexicographically. Please by all means if this implementation sounds incorrect or inefficient let me know, I am just starting to learn elastic search!

My initial approach was to use a terms aggregation, with the hazardType.keyword as the field, the set the size to 1, and order according to find the highest or lowest alphabetical hazardType in the bucket, then try to somehow apply this to the bucket_sort.

    query["aggs"]["clustered_hazards"]["aggs"]["first_hazard_type"]  = {
        "terms": {
            "field": "hazardType.keyword",
            "size": 1,
            "order": {"_key": "asc" or "desc"}
        }
    } 

Here is an example of the query I use for likelihood desc:

"query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "likelihood": {
              "gte": 3,
              "lte": 4
            }
          }
        },
        {
          "range": {
            "articleScoreValue": {
              "gte": 7,
              "lte": 10
            }
          }
        },
        {
          "range": {
            "publishedAt": {
              "gte": "2024-12-01",
              "lte": "2024-12-02"
            }
          }
        },
        {
          "terms": {
            "hazardType.keyword": [
              "WILDFIRE",
              "ACCIDENT",
              "CYCLONE"
            ]
          }
        },
      ]
    }
  },
  "aggs": {
    "unique_clusters": {
      "cardinality": {
        "field": "clusterId"
      }
    },
    "clustered_hazards": {
      "terms": {
        "field": "clusterId",
        "size": 10000
      },
      "aggs": {
        "top_hazards": {
          "top_hits": {
            "size": 6,
            "sort": [
              {
                "likelihood": {
                  "order": "desc"
                }
              }
            ],
            "_source": [
              "id",
              "title",
              "clusterId",
              "likelihood",
              "articleScoreValue",
              "publishedAt",
              "hazardType"
            ]
          }
        },
        "paginated_top_hazards": {
          "bucket_sort": {
            "sort": [
              {
                "top_likelihood": "desc"
              }
            ],
            "from": 0,
            "size": 4
          }
        },
        "top_likelihood": {
          "max": {
            "field": "likelihood"
          }
        }
      }
    }
  }

And here is an example of the query I am trying to use for hazardType sorting:

  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "likelihood": {
              "gte": 3,
              "lte": 4
            }
          }
        },
        {
          "range": {
            "articleScoreValue": {
              "gte": 7,
              "lte": 10
            }
          }
        },
        {
          "range": {
            "publishedAt": {
              "gte": "2024-12-01",
              "lte": "2024-12-02"
            }
          }
        },
        {
          "terms": {
            "hazardType.keyword": [
              "WILDFIRE",
              "ACCIDENT",
              "CYCLONE"
            ]
          }
        },
      ]
    }
  },
  "aggs": {
    "unique_clusters": {
      "cardinality": {
        "field": "clusterId"
      }
    },
    "clustered_hazards": {
      "terms": {
        "field": "clusterId",
        "size": 10000
      },
      "aggs": {
        "top_hazards": {
          "top_hits": {
            "size": 6,
            "sort": [
              {
                "hazardType.keyword": {
                  "order": "desc"
                }
              }
            ],
            "_source": [
              "id",
              "title",
              "clusterId",
              "likelihood",
              "articleScoreValue",
              "publishedAt",
              "hazardType"
            ]
          }
        },
        "paginated_top_hazards": {
          "bucket_sort": {
             // Somehow apply the first_hazard_type sort here
            "sort": [{"first_hazard_type": {"order": "desc"}}],
            "from": 0,
            "size": 4
          }
        },
        "first_hazard_type": {
          "terms": {
            "field": "hazardType.keyword",
            "size": 1,
            "order": {
              "_key": "desc"
            }
          }
        }
      }
    }
  }