# Sort Terms Aggregation By Parent Docs Count

**URL:** <https://discuss.elastic.co/t/sort-terms-aggregation-by-parent-docs-count/84799>\
**Category:** Elasticsearch\
**Created:** [May 6, 2017, 12:55am UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-parent-docs-count/84799 "2017-05-06T00:55:56Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![panda2004](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panda2004/32/19473_2.png) [@panda2004](https://discuss.elastic.co/u/panda2004)\
**Post date:** [May 6, 2017, 12:55am UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-parent-docs-count/84799/1 "2017-05-06T00:55:56Z")

</div>

Hey there,

As far as I know, terms aggregations are probably the most memory intensive and less accurate search request in ElasticSearch. Having said that, I would like to get guidelines concerning the following problem.

## Document Structure

Let's say I have an index contains of information about movies and actors. It means that I have 1 document type - Movie, and each movie document is related to nested documents containing the actors of this movie.  
You can see an illustration below:

 ![](https://us1.discourse-cdn.com/elastic/original/3X/e/0/e0e06c7c6727a7fea4de4623f803b63a0f5cafe2.png)

## The Search Requirement

Let's say I have a search query **to get the top 10 actors which participated the most in a specific group of movies**. That means that I have a query that narrows down the movies by using queries (range query, filter query) and then search for the requested actors by using aggregations (a main terms aggregation and a a value count sub aggregation).  
It would be something like this:

 ![](https://us1.discourse-cdn.com/elastic/original/3X/a/8/a84004c99389c07d1eb3a9d0a642df4aa6cb48f7.PNG)

## The Problem

**In the real world, there could be O(N) movies and O(N) actors. Because I want that each main bucket be sorted by its sub bucket, there could be N^2 buckets created by this search request.**

That raises the following questions:

1. Does this type of queries even possible in ElasticSearch?

2. Is there any optimization I could use in the terms aggregation to optimize the search request? I mean playing with terms aggregation parameters such as collect mode, execution hit and so forth.

3. Is there any optimization I could use for improving other ElasticSearch components for serving this query? I mean mapping settings (such as eager\_global\_ordinals) or other index settings (such as caching).

4. **It feels like there is an alternative: replacing the terms aggregation with something like top\_hits aggregation of significant\_terms aggregation, but I haven't figure it out how.**

I really appreciate any help you can provide,  
Idan

---

<div class="post-metadata">

**Author:** ![panda2004](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/panda2004/32/19473_2.png) [@panda2004](https://discuss.elastic.co/u/panda2004)\
**Post date:** [May 6, 2017, 2:01am UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-parent-docs-count/84799/2 "2017-05-06T02:01:17Z")

</div>

I thought about my problem again and I have a few insights.

- An actor can't belong to a movie more than once (and that's exactly the bug I have in my system now). Therefore, sorting is probably not needed: the top 10 actors are actually those who played in most of the movies.
- In practical, the query I wrote probably isn't acceptable anyway in ElasticSearch - because of a known issue: [16838](https://github.com/elastic/elasticsearch/issues/16838#issuecomment-190210463)

---

<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:** [June 3, 2017, 2:01am UTC](https://discuss.elastic.co/t/sort-terms-aggregation-by-parent-docs-count/84799/3 "2017-06-03T02:01:30Z")

</div>

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