I have a mapping of businesses, for which I am using an aggregation to calculate average ratings for the businesses grouped by business id. Sorting the aggregation by business id or average rating works fine. My issue is that I would like to have the ability to also sort by business name. I have tried to create a sub aggregation on id to create a name bucket and sort by that. But I understand that I can only sort by a single bucket or metric aggregation value.
Mapping
{
"properties": {
"id": {"type": "keyword"},
"name": {
"type": "text",
"fields": {
"kw": {"type": "keyword"}
}
},
"reviews": {
"type": "nested",
"properties": {
"id": {"type": "keyword"},
"rating": {"type": "float"}
}
}
}
}
Search
{
"query": {
"match": {
"name": {
"query": "plumbing"
}
}
},
"aggs": {
"stats": {
"terms": {
"field": "id",
"size": 2,
"order": {"name": "desc"}
},
"aggs": {
"reviews": {
"nested": {
"path": "reviews"
},
"aggs": {
"average": {
"avg": {"field": "reviews.rating"}
}
}
},
"name": {
"terms": {
"field": "name.kw"
}
}
}
}
}
When I use the below search on this mapping, I get the following error:
Error: aggregation_execution_exception
Reason: Invalid aggregation order path [name]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end.