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.
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" }