# How to sort aggregated based on sub aggregation's doc\_count? **URL:** \ **Category:** Elasticsearch\ **Created:** [November 12, 2020, 10:25am UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195 "2020-11-12T10:25:41Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![lessless](https://avatars.discourse-cdn.com/v4/letter/l/bc8723/32.png) [@lessless](https://discuss.elastic.co/u/lessless)\ **Post date:** [November 12, 2020, 10:25am UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/1 "2020-11-12T10:25:41Z")
Hi, I'm completely stuck trying to figure out how to sort "users" based on "status\_codes" in the following query: ```auto { "size": 0, "aggs": { "users": { "terms": { "field": "user_id" }, "aggs": { "status_codes": { "filters": { "filters": { "200s": { "range": { "status_code": { "gte": 200, "lte": 299 } } }, "400s": { "range": { "status_code": { "gte": 400, "lte": 499 } } }, "500s": { "range": { "status_code": { "gte": 500, "lte": 599 } } } } } }, "bucket_truncate": { "bucket_sort": { "sort": [ ], "from": 0, "size": 10 } } } } } } ``` I want "users", that have the most "400s", for example, to come first. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 12, 2020, 11:41am UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/2 "2020-11-12T11:41:05Z")
It's probably easier to flip things the other way - ie status codes at the top level aggregation and users as the aggregation underneath. ---
**Author:** ![lessless](https://avatars.discourse-cdn.com/v4/letter/l/bc8723/32.png) [@lessless](https://discuss.elastic.co/u/lessless)\ **Post date:** [November 12, 2020, 12:49pm UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/3 "2020-11-12T12:49:08Z")
Will results answer the same question: give me the next 100 `users` starting from 400th user with their `status_codes` ordered by `200s/400s/500s` ? ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 12, 2020, 1:07pm UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/4 "2020-11-12T13:07:32Z")
When you add deep pagination as a requirement and you’re talking about a distributed system that becomes a problem. You may me better suited creating an entity-centric index from the log data to do user behaviour analysis. This can be done using [transforms](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html). ---
**Author:** ![lessless](https://avatars.discourse-cdn.com/v4/letter/l/bc8723/32.png) [@lessless](https://discuss.elastic.co/u/lessless)\ **Post date:** [November 12, 2020, 7:56pm UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/5 "2020-11-12T19:56:00Z")
Understood! Can you please give an example of the query if we throw pagination out of the equation? This is a non-trivial thing and I'm willing to learn how it's done. Maybe it will help me understand Elastic better. ---
**Author:** ![lessless](https://avatars.discourse-cdn.com/v4/letter/l/bc8723/32.png) [@lessless](https://discuss.elastic.co/u/lessless)\ **Post date:** [November 13, 2020, 9:13am UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/6 "2020-11-13T09:13:09Z")
what puzzles me out is the idea of flipping an aggregation. it seems counter-intuitive to me and I would love to understand why it's the advisable way to go. ---
**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\ **Post date:** [November 13, 2020, 9:41am UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/7 "2020-11-13T09:41:22Z")
> [@lessless](#): > > I would love to understand why it's the advisable way to go. It’s about working more easily with the natural sort order of ‘ terms’ aggs. They pick the most popular. With users at the top level you’ll find the most prolific user first and for each the most common status code for that user. With the status codes up top up you’ll get the most common code first (likely 200) and for each status code the most common user. So for 404s you’ll naturally get _the users with the most 404s_ This might highlight a user who has a lot of 404s but they also have many more 200s to make up for it (ie 404s are only 1% of their total traffic). To find users who have an unusually high mix of a status code (eg 90% of their traffic is 404s) simply use a ‘significant\_terms’ agg instead of a ‘terms’ agg for the users. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [December 11, 2020, 9:41am UTC](https://discuss.elastic.co/t/how-to-sort-aggregated-based-on-sub-aggregations-doc-count/255195/8 "2020-12-11T09:41:25Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.