Sort elasticsearch aggregation buckets by text field

Question

I'm trying to sort the result buckets of an elasticsearch aggregation. I have a large set of documents:

"mappings": {
    "properties": {
        "price": {
            "type": "double"
        },
        "product_name": {
            "type": "text"
        },
        "product_id": {
            "type": "keyword"
        },
        "timestamp": {
            "type": "date"
        }
    }
}

What I'm currently doing is getting the latest sell for each product_id using composite and top_hits aggregations:

{
    "query": {
        "bool": {
            "filter": [
                {
                    "range": {
                        "timestamp": {
                            "gte": "2019-10-25T00:00:00Z",
                            "lte": "2019-10-26T00:00:00Z"
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "distinct_products": {
            "composite": {
                "sources": [
                    {
                        "distinct_ids": {
                            "terms": {
                                "field": "product_id"
                            }
                        }
                    }
                ],
                "size": 10000
            },
            "aggs": {
                "last_timestamp": {
                    "top_hits": {
                        "sort": {
                            "timestamp": {
                                "order": "desc"
                            }
                        },
                        "size": 1
                    }
                }
            }
        }
    }
}

Now I want to sort the resulting buckets by an arbitrary field. If I want to sort by price, I can use the solution in this question by adding a max aggregation which extracts the product_price field from each bucket, and a bucket_sort aggregation at the end which will sort the results of max:

{
    "query": {
        "bool": {
            "filter": [
                {
                    "range": {
                        "timestamp": {
                            "gte": "2019-10-25T00:00:00Z",
                            "lte": "2019-10-26T00:00:00Z"
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "distinct_products": {
            "composite": {
                "sources": [
                    {
                        "distinct_ids": {
                            "terms": {
                                "field": "product_id"
                            }
                        }
                    }
                ],
                "size": 10000
            },
            "aggs": {
                "last_timestamp": {
                    "top_hits": {
                        "sort": {
                            "timestamp": {
                                "order": "desc"
                            }
                        },
                        "size": 1,
                        "_source": {
                            "excludes": []
                        }
                    }
                },
                "latest_sell": {
                    "max": {
                        "field": "product_price"
                    }
                },
                "latest_sell_secondary": {
                    "max": {
                        "field": "timestamp"
                    }
                },
                "sort_sells": {
                    "bucket_sort": {
                        "sort": {
                            "latest_sell": {
                                "order": "desc"
                            },
                            "latest_sell_secondary": {
                                "order": "desc"
                            }
                        },
                        "from": 0,
                        "size": 10000
                    }
                }
            }
        }
    }
}

If I want to sort alphabetically by product_name instead of product_price, I cannot use the max aggregation since it only works on numeric fields.

What can I do to sort the last_timestamp buckets (each with only one document) by a text field?

The elasticsearch version I'm using is 7.2.0.

Answer 1

From docs

Each bucket may be sorted based on its _key, _count or its sub-aggregations

Instead of product Id you can use product_name.keyword in terms aggregation and sort on the key

"order": { "_key" : "asc" }