Elasticsearch: Bucket Sort Aggregation + Sorting on Alphabetical Field

Question

I am trying to club data on field and that should be sorted on another field. I also want pagination so I thought I could use BucketSort of elasticsearch. I am facing a problem with the String(Alphabetical) Filed.

Here is my dummy data.

{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp001_local000000000000001",
    "_score": 10.0,
    "_source": {
        "name": [
            "Person 01"
        ],
        "groupbyid": [
            "group0001"
        ],
        "ranking": [
             "2.0"
        ]
    }
},
{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp002_local000000000000001",
    "_score": 85146.375,
    "_source": {
        "name": [
            "Person 02"
        ],
        "groupbyid": [
            "group0001"
        ],
        "ranking": [
             "10.0"
        ]
    }
},
{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp003_local000000000000001",
    "_score": 20.0,
    "_source": {
        "name": [
            "Person 03"
        ],
        "groupbyid": [
            "group0002"
        ],        
        "ranking": [
             "-1.0"
        ]
    }
},
{
    "_index": "testing-aggregation",
    "_type": "employee",
    "_id": "emp004_local000000000000001",
    "_score": 5.0,
    "_source": {
        "name": [
            "Person 04"
        ],
        "groupbyid": [
            "group0002"
        ],
        "ranking": [
             "2.0"
        ]
    }
}

Mapping for the above data.

{
    "name": {
        "type": "text",
        "fielddata": true,
        "fields": {
            "lower_case_sort": {
                "type": "text",
                "fielddata": true,
                "analyzer": "case_insensitive_sort"
            }
        }
    },
   "ranking": {
         "type": "float"
    },
    "groupbyid": {
        "type": "text",
        "fielddata": true,
        "index": "analyzed",
        "fields": {
            "raw": {
                "type": "keyword",
                "index": "not_analyzed"
            }
        }
    }
}

ES Query:

{
  "from": 0,
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "name:XYZ"
          }
        }
      ]
    }
  },
  "aggregations": {
    "groupbyid": {
      "terms": {
        "field": "groupbyid.raw",
        "size": 100
      },
      "aggs": {
        "top_hit_agg": {
          "top_hits": {
            "size": 100
          }
        },
        "ranking_agg": {
            "min": {
                "field": "ranking"
            }
        },
        "test_bucket_sort": {
          "bucket_sort": {
            "sort": [
              {
                "ranking_agg": {
                  "order": "desc"
                }
              }
            ],
            "size": 100,
            "from": 0
          }
        }
      }
    }
  }
}

I am able to achieve for the Numeric field. But do not have an idea how will I do for the name field. One way is to use the script, but I do not want to go with this approach as it might be an expensive operation.

Can anyone help me with this? I am using ES 7.7.1.

Thank you, Sharvil Shah

Answer 1

If you want to sort name field alphabetically, then instead of groupbyid you can use name.keyword in terms aggregation and sort on the key.

You cannot use the name field in min aggregation, as text fields are not supported for Min aggregation

{
  "aggregations": {
    "groupbyname": {
      "terms": {
        "field": "name.keyword",
        "order": { "_key" : "desc" }
      }
    }
  }
}

Search Result:

"aggregations": {
    "groupbyname": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "Person 04",
          "doc_count": 1
        },
        {
          "key": "Person 03",
          "doc_count": 1
        },
        {
          "key": "Person 01",
          "doc_count": 1
        }
      ]
    }