# Elasticsearch, terms aggs according to sibling nested fields **URL:** \ **Category:** Elasticsearch\ **Created:** [January 23, 2020, 4:40am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174 "2020-01-23T04:40:19Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\ **Post date:** [January 23, 2020, 4:40am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174/1 "2020-01-23T04:40:19Z")
Elasticsearch v7.5 Hello and good day! We have 2 indices named **socialmedia** and **influencers** Sample contents: **socialmedia** : ```auto { '_id' : 1001, 'title' : "Title 1", 'smp_id' : 1, "latest" : [ { "soc_mm_score" : "5", } ] }, { '_id' : 1002, 'title' : "Title 2", 'smp_id' : 2, "latest" : [ { "soc_mm_score" : "10", } ] }, { '_id' : 1003, 'title' : "Title 3", 'smp_id' : 3, "latest" : [ { "soc_mm_score" : "35", } ] }, { '_id' : 1004, 'title' : "Title 4", 'smp_id' : 2, "latest" : [ { "soc_mm_score" : "30", } ] } ``` //omitted some other fields **influencers** : ```auto { '_id' : 1, 'name' : "John", 'smp_id' : 1 }, { '_id' : 2, 'name' : "Peter", 'smp_id' : 2 }, { '_id' : 3, 'name' : "Mark", 'smp_id' : 3 } ``` Now I have this simple query that determines which _documents_ in the **socialmedia** index has the most **latest.soc\_mm\_score** value, and also displaying their corresponding **influencers** determined by the **smp\_id** ```auto GET socialmedia/_search { "size": 0, "_source": "latest", "query": { "match_all": {} }, "aggs": { "LATEST": { "nested": { "path": "latest" }, "aggs": { "MM_SCORE": { "terms": { "field": "latest.soc_mm_score", "order": { "_key": "desc" }, "size": 3 }, "aggs": { "REVERSE": { "reverse_nested": {}, "aggs": { "SMP_ID": { "top_hits": { "_source": ["smp_id"], "size": 1 } } } } } } } } } } ``` **SAMPLE OUTPUT** : ```auto "aggregations" : { "LATEST" : { "doc_count" : //omitted, "MM_SCORE" : { "doc_count_error_upper_bound" : //omitted, "sum_other_doc_count" : //omitted, "buckets" : [ { "key" : 35, "doc_count" : 1, "REVERSE" : { "doc_count" : 1, "SMP_ID" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "socialmedia", "_type" : "index", "_id" : "1003", "_score" : 1.0, "_source" : { "smp_id" : "3" } } ] } } } }, { "key" : 30, "doc_count" : 1, "REVERSE" : { "doc_count" : 1, "SMP_ID" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "socialmedia", "_type" : "index", "_id" : "1004", "_score" : 1.0, "_source" : { "smp_id" : "2" } } ] } } } }, { "key" : 10, "doc_count" : 1, "REVERSE" : { "doc_count" : 1, "SMP_ID" : { "hits" : { "total" : { "value" : 1, "relation" : "eq" }, "max_score" : 1.0, "hits" : [ { "_index" : "socialmedia", "_type" : "index", "_id" : "1002", "_score" : 1.0, "_source" : { "smp_id" : "2" } } ] } } } } ] } } } ``` with the query above, I was able to successfully display **which documents** have the highest **latest.soc\_mm\_score** values The sample output above only displays **DOCUMENTS** , telling that the **influencers (a.k.a smp\_id)** related to them are the TOP INFLUENCERS according to **latest.soc\_mm\_score** Ideally just by using this aggs query, ```auto "terms" : { "field" : "smp_id" } ``` portrays the concept of which influencers are the **top** according to the **doc\_count** Now, displaying the terms query according to **latest.soc\_mm\_score** displays **TOP DOCUMENTS** ```auto "terms" : { "field" : "latest.soc_mm_score" } ``` **REAL OBJECTIVE** : I want to display the **TOP INFLUENCERS** according to the **latest.soc\_mm\_count** in the **socialmedia** index. If Elasticsearch can count all the documents where according to unique smp\_id, is there a way for ES to sum all **latest.soc\_mm\_score** values and use it as **terms**? My objective above should output these: - smp\_id 2 as the Top Influencer because he has 2 posts (with soc\_mm\_score of 30 and 10), adding them gets him 40 soc\_mm\_score - smp\_id 3 as the 2nd Top Influencer, he has 1 post with 35 soc\_mm\_score - smp\_id 1 as the 3rd Top Influencer, he has 1 post with 5 soc\_mm\_score Is there a proper query to meet this objective? ---
**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\ **Post date:** [January 30, 2020, 2:26am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174/2 "2020-01-30T02:26:22Z")
Any ideas about this question, any ES admins or devs? ---
**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\ **Post date:** [February 4, 2020, 2:19am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174/3 "2020-02-04T02:19:34Z")
up, anyone who has experienced this please? ---
**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\ **Post date:** [February 13, 2020, 2:25am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174/4 "2020-02-13T02:25:49Z")
Anyone from the respected devs / admins please?? ---
**Author:** ![Noctis17](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/noctis17/32/41656_2.png) [@Noctis17](https://discuss.elastic.co/u/Noctis17)\ **Post date:** [February 17, 2020, 10:17am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174/5 "2020-02-17T10:17:05Z")
FINALLY! FOUND AN ANSWER!!! ```auto "aggs": { "INFS": { "terms": { "field": "smp_id.keyword", "order": { "LATEST > SUM_SVALUE": "desc" } }, "aggs": { "LATEST": { "nested": { "path": "latest" }, "aggs": { "SUM_SVALUE": { "sum" : { "field": "latest.soc_mm_score" } } } } } } } ``` **Displays the following sample:** [![FINAL](https://us1.discourse-cdn.com/elastic/original/3X/8/f/8f3f99415fc1a1934364220cae4c5e00530d0820.png)](https://i.stack.imgur.com/xpzB5.png) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 16, 2020, 10:17am UTC](https://discuss.elastic.co/t/elasticsearch-terms-aggs-according-to-sibling-nested-fields/216174/6 "2020-03-16T10:17:07Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.