Hi!
Is it possible to sort aggregated buckets using inner aggregation result fields?
Example: we have posts aggregated by topicId using composite aggregation named "topic_aggregation".
Inside it using nested 'top_hits' aggregation we select top 1 post by created_date and return buckets each having only one post per thread, named "post_aggregation".
Following query works:
{
"aggs": {
"main_aggregation": {
"aggs": {
"post_aggregation": {
"top_hits": {
"size": 1,
"sort": [
{
"createdDate": {
"order": "desc"
}
}
]
}
}
},
"composite": {
"size": 1000,
"sources": [
{
"topic_aggregation": {
"terms": {
"field": "threadId",
"order": "asc"
}
}
}
]
}
}
},
"query": {
"match_all": {}
},
"size": 0
}
and it return results similar to this:
{
"aggregations": {
"main_aggregation": {
"after_key": {
"topic_aggregation": 42521
},
"buckets": [
{
"key": {
"topic_aggregation": 42520
},
"doc_count": 3,
"post_aggregation": {
"hits": {
"total": {
"value": 3,
"relation": "eq"
},
"max_score": null,
"hits": [
{
"_index": "index1",
"_type": "_doc",
"_id": "42520 T",
"_score": null,
"_source": {
"type": 1,
"postId": 0,
"threadId": 42520,
"createdDate": "2005-05-19T22:38:00"
},
"sort": [
1116542280000
]
}
]
}
}
},
{
"key": {
"topic_aggregation": 42521
},
"doc_count": 6,
"post_aggregation": {
"hits": {
"total": {
"value": 6,
"relation": "eq"
},
"max_score": null,
"hits": [
{
"_index": "index1",
"_type": "_doc",
"_id": "42521 T",
"_score": null,
"_source": {
"type": 1,
"postId": 0,
"threadId": 42521,
"createdDate": "2005-04-14T15:03:00"
},
"sort": [
1113490980000
]
}
]
}
}
}
]
}
}
}
however, sometimes we need to sort resulting buckets by the value of the document contained inside it, the one produced with inner aggregation 'top_hits'.
For example order the buckets not by threadId used in outer aggregation, but by "createdDate" field inside inner top 1 document.
I cant figure out how to form a path to that field.
Tried the following, but it says path is incorrect:
"type": "aggregation_execution_exception",
"reason": "Invalid path element [post_aggregation.hits.hits[0]._source.createdDate] in path [post_aggregation.hits.hits[0]._source.createdDate]"
example of query with sorting:
{
"aggs": {
"main_aggregation": {
"aggs": {
"post_aggregation": {
"top_hits": {
"size": 1,
"sort": [
{
"createdDate": {
"order": "desc"
}
}
]
}
},
"bucket_sort": {
"bucket_sort": {
"from": 0,
"size": 20,
"sort": [
{
"post_aggregation.hits.hits[0]._source.createdDate": {
"order": "desc"
}
}
]
}
}
},
"composite": {
"size": 1000,
"sources": [
{
"topic_aggregation": {
"terms": {
"field": "threadId",
"order": "asc"
}
}
}
]
}
}
},
"query": {
"match_all": {}
},
"size": 0
}