# Sort by top\_hits aggregation

**URL:** <https://discuss.elastic.co/t/sort-by-top-hits-aggregation/353605>\
**Category:** Elastic Search\
**Tags:** elastic-app-search\
**Created:** [February 19, 2024, 2:16pm UTC](https://discuss.elastic.co/t/sort-by-top-hits-aggregation/353605 "2024-02-19T14:16:21Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![martid1703](https://avatars.discourse-cdn.com/v4/letter/m/2bfe46/32.png) [@martid1703](https://discuss.elastic.co/u/martid1703)\
**Post date:** [February 19, 2024, 2:16pm UTC](https://discuss.elastic.co/t/sort-by-top-hits-aggregation/353605/1 "2024-02-19T14:16:21Z")

</div>

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:

```auto
{
    "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:

```auto
{
    "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:

```auto
    "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:

```auto
{
    "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
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 18, 2024, 2:17pm UTC](https://discuss.elastic.co/t/sort-by-top-hits-aggregation/353605/2 "2024-03-18T14:17:02Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.