# Combination of two top terms aggregations **URL:** \ **Category:** Elasticsearch\ **Created:** [February 19, 2018, 3:29pm UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479 "2018-02-19T15:29:37Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![aurelien](https://avatars.discourse-cdn.com/v4/letter/a/67e7ee/32.png) [@aurelien](https://discuss.elastic.co/u/aurelien)\ **Post date:** [February 19, 2018, 3:29pm UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479/1 "2018-02-19T15:29:38Z")
Hi, When I perform a term aggregation on FIELD1 and a term aggregation on FIELD2, I got the following result: > FIELD1 : { > TOPTERM1FIELD1 : 10000, > TOPTERM2FIELD1 : 1000 > } > and > FIELD2 : { > TOPTERM1FIELD2 : 121212, > TOPTERM2FIELD2 : 1212 > } If I combine the two aggregations with the help of the "sub-aggregation" mechanism, I get : > FIELD1 : { > TOPTERM1FIELD1 : { > FIELD2 : { > LOCALTOPTERM1FIELD2: 12 > } > } > } etc.... I would like to have all combinations of the n first terms for the two top terms: For exemple : > TOPTERM1FIELD1 AND TOPTERM1FIELD2 : 12 > TOPTERM1FIELD1 AND TOPTERM2FIELD2 : 4 I tried the composite aggregation to achieve this but it seems that it collects ordered terms and not most frequent terms first. Is there a way to achieve this? Thank you for your help, Aurélien ---
**Author:** ![aurelien](https://avatars.discourse-cdn.com/v4/letter/a/67e7ee/32.png) [@aurelien](https://discuss.elastic.co/u/aurelien)\ **Post date:** [February 19, 2018, 3:35pm UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479/2 "2018-02-19T15:35:55Z")
I tried to use the" order by count desc" available in classic term aggregation : `{ "size": 0, "aggs": { "test_composite": { "composite": { "sources": [{ "IPH": { "terms": { "field": "FIELD", "order": { "_count": "desc" } } } }] } } } }` but it didn't work... ☹ ---
**Author:** ![aurelien](https://avatars.discourse-cdn.com/v4/letter/a/67e7ee/32.png) [@aurelien](https://discuss.elastic.co/u/aurelien)\ **Post date:** [February 19, 2018, 5:21pm UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479/3 "2018-02-19T17:21:39Z")
I have realized that my explanation wasn't very clear so I add a concrete example: When I run the 2 aggregations separatly : ``` { "aggs" : { "IPH": { "terms" : { "field": "IPH", "size" : 2} } , "PID": { "terms" : { "field": "PID" , "size" : 2} } } } ``` I got the following result: ``` { "aggregations": { "IPH": { "buckets": [{ "key": "035/00", "doc_count": 722 }, { "key": "043/00", "doc_count": 559 } ] }, "PID": { "buckets": [{ "key": "960897", "doc_count": 170 }, { "key": "71628332", "doc_count": 147 } ] } } } ``` When I try to combine them with a sub-aggregation : ``` { "aggs": { "IPH": { "terms": { "field": "IPH", "size": 2 }, "aggs": { "PID": { "terms": { "field": "PID", "size": 2 } } } } } } ``` I have the following result: ``` { "aggregations": { "IPH": { "buckets": [{ "key": "035/00", "doc_count": 722, "PID": { "buckets": [{ "key": "906768", "doc_count": 51 }, { "key": "3975774", "doc_count": 49 } ] } }, { "key": "043/00", "doc_count": 559, "PID": { "buckets": [{ "key": "3975774", "doc_count": 49 }, { "key": "640822", "doc_count": 33 } ] } } ] } } } ``` I am trying to find a way to get ``` [{ "IPH": "035/00", "PID": "960897", "count": 12 }, { "IPH": "035/00", "PID": "71628332", "count": 12 }, { "IPH": "043/00", "PID": "960897", "count": 12 }, { "IPH": "043/00", "PID": "71628332", "count": 12 }] ``` Does anybody have an idea how to achieve this? Thank you, ---
**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:** [February 20, 2018, 9:35am UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479/4 "2018-02-20T09:35:30Z")
> [@aurelien](#): > > Does anybody have an idea how to achieve this? So you essentially want to flatten the results (I don't know where the count of 12 comes from)? You can use a `script` in a single terms aggregation to combine the PID and IPH values into a single string that acts as the key you are looking for. ---
**Author:** ![aurelien](https://avatars.discourse-cdn.com/v4/letter/a/67e7ee/32.png) [@aurelien](https://discuss.elastic.co/u/aurelien)\ **Post date:** [February 20, 2018, 6:04pm UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479/5 "2018-02-20T18:04:24Z")
Hi, Thank you for your answer. The count 12 is probably false, but basically, for ``` { "IPH": "035/00", "PID": "960897", "count": XX } ``` XX should be the count of documents matching the query : (IPH:"035/00" AND PID:"960897") Let's assume that the count 12 is correct. Maybe I misunderstood your solution, but I think there is a little problem with it for my use case. If I am right, with the script it is possible to have something like : ``` { "key" : "032/00_898989", doc_count" : 231 } ``` because the intersection between IPH:"032/00" and PID:"898989" contains more than 12 documents. However, this is not what I want because, separately, "035/00" is not in the top 2 terms for IPH and "898989" is not in the top 2 terms for PID, event if "035/00\_898989" is in the top 2 terms for my query. Thank you for your feedback, Regards, Aurélien ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 20, 2018, 6:04pm UTC](https://discuss.elastic.co/t/combination-of-two-top-terms-aggregations/120479/6 "2018-03-20T18:04:33Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.