# Terms aggregation on a nested field for calculating stats based on a parent-field **URL:** \ **Category:** Elasticsearch\ **Created:** [June 20, 2020, 6:37pm UTC](https://discuss.elastic.co/t/terms-aggregation-on-a-nested-field-for-calculating-stats-based-on-a-parent-field/237932 "2020-06-20T18:37:42Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Andy1990](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andy1990/32/45433_2.png) [@Andy1990](https://discuss.elastic.co/u/Andy1990)\ **Post date:** [June 20, 2020, 6:37pm UTC](https://discuss.elastic.co/t/terms-aggregation-on-a-nested-field-for-calculating-stats-based-on-a-parent-field/237932/1 "2020-06-20T18:37:42Z")
Hello, in my use case i have documents with a client and a price. The client is a nested object (containing id, and acronym), the price is on the first json level of my source). I would like to calculate metric values (i.e. stats metrics) grouped by client.id. The mapping looks as follows: ```auto PUT /test { "mappings": { "properties": { "client": { "type": "nested", "properties": { "id": { "type": "long" } } }, "price": { "type": "long" } } } } ``` Then i inserted 3 documents. The field client2 is the same as client, but it is not explicitly defined as nested in the mapping (for learning purposes). : ```auto PUT /test/_doc/1 { "client": { "id": 1, "acronym": "O" }, "client2": { "id": 1, "acronym": "O" }, "price": 10 } PUT /test/_doc/2 { "client": { "id": 2, "acronym": "U" }, "client2": { "id": 2, "acronym": "U" }, "price": 10 } PUT /test/_doc/3 { "client": { "id": 2, "acronym": "U" }, "client2": { "id": 2, "acronym": "U" }, "price": 10 } ``` Now the query for getting the stats per "client2.id" is: ```auto POST /test/_search { "size": 0, "aggs": { "GROUP_BY_CLIENT_ID": { "terms": { "field": "client2.id", "min_doc_count": 0 }, "aggs": { "STATS_FOR_CLIENT": { "stats": { "field": "price" } } } } } } ``` And i get the result i want: ```auto "aggregations" : { "GROUP_BY_CLIENT_ID" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 2, "doc_count" : 2, "STATS_FOR_CLIENT" : { "count" : 2, "min" : 10.0, "max" : 10.0, "avg" : 10.0, "sum" : 20.0 } }, { "key" : 1, "doc_count" : 1, "STATS_FOR_CLIENT" : { "count" : 1, "min" : 10.0, "max" : 10.0, "avg" : 10.0, "sum" : 10.0 } } ] } } ``` The same query (only replace "client2.id" with "client.id" is not working and gives me 0 elements in both buckets. Query: ```auto POST /test/_search { "size": 0, "aggs": { "GROUP_BY_CLIENT_ID": { "terms": { "field": "client.id", "min_doc_count": 0 }, "aggs": { "STATS_FOR_CLIENT": { "stats": { "field": "price" } } } } } } ``` Result: ```auto "aggregations" : { "GROUP_BY_CLIENT_ID" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 1, "doc_count" : 0, "STATS_FOR_CLIENT" : { "count" : 0, "min" : null, "max" : null, "avg" : null, "sum" : 0.0 } }, { "key" : 2, "doc_count" : 0, "STATS_FOR_CLIENT" : { "count" : 0, "min" : null, "max" : null, "avg" : null, "sum" : 0.0 } } ] } } ``` Using the "client2.id" approach is not the solution, because i need also to make sure to search in a nested way. So removing the definition of "nested" in the mapping for the "client" field is not an option. As "client" is explicitly defined as "nested" in the mapping, i am forced to do a nested aggregation according to the solution of this [post](https://discuss.elastic.co/t/terms-aggregation-not-working-for-nested-fields/62799) . I tried to merge this solution with my issue, but i don't get the result i without stats. Query: ```auto POST /test/_search { "size": 0, "aggs": { "GROUP_BY_CLIENT": { "nested": { "path": "client" }, "aggs": { "GROUP_BY_CLIENT_ID": { "terms": { "field": "client.id", "min_doc_count": 0 }, "aggs": { "STATS_FOR_CLIENT_PRICES": { "stats": { "field": "price" } } } } } } } } ``` In the result i get now the right query counts, but not the right stats. The result looks like: ```auto "aggregations" : { "GROUP_BY_CLIENT" : { "doc_count" : 3, "GROUP_BY_CLIENT_ID" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 2, "doc_count" : 2, "STATS_FOR_CLIENT_PRICES" : { "count" : 0, "min" : null, "max" : null, "avg" : null, "sum" : 0.0 } }, { "key" : 1, "doc_count" : 1, "STATS_FOR_CLIENT_PRICES" : { "count" : 0, "min" : null, "max" : null, "avg" : null, "sum" : 0.0 } } ] } } } ``` I think the problem is the price, which is one level higher than the nested "client" context. What is the query in order to group the stats by "client.id" in this use case? Any ideas or approaches out there? Thanks a lot for your help and best regards Andy ---
**Author:** ![Andy1990](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andy1990/32/45433_2.png) [@Andy1990](https://discuss.elastic.co/u/Andy1990)\ **Post date:** [June 22, 2020, 10:01am UTC](https://discuss.elastic.co/t/terms-aggregation-on-a-nested-field-for-calculating-stats-based-on-a-parent-field/237932/2 "2020-06-22T10:01:58Z")
I was able to solve it by myself by using [reverse nested aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-reverse-nested-aggregation.html). The query looks now like: ```auto POST /test/_search { "size": 0, "aggs": { "GO_INTO_CLIENT": { "nested": { "path": "client" }, "aggs": { "GROUP_BY_CLIENT_ID": { "terms": { "field": "client.id", "min_doc_count": 0 }, "aggs": { "GO_FROM_CLIENT_TO_ROOT": { "reverse_nested": {}, "aggs": { "PRICE_STATS": { "stats": { "field": "price" } } } } } } } } } } ``` And this gives me the result i want: ```auto "aggregations" : { "GROUP_BY_CLIENT" : { "doc_count" : 3, "GROUP_BY_CLIENT_ID" : { "doc_count_error_upper_bound" : 0, "sum_other_doc_count" : 0, "buckets" : [ { "key" : 2, "doc_count" : 2, "GO_FROM_CLIENT_TO_ROOT" : { "doc_count" : 2, "PRICE_STATS" : { "count" : 2, "min" : 10.0, "max" : 10.0, "avg" : 10.0, "sum" : 20.0 } } }, { "key" : 1, "doc_count" : 1, "GO_FROM_CLIENT_TO_ROOT" : { "doc_count" : 1, "PRICE_STATS" : { "count" : 1, "min" : 10.0, "max" : 10.0, "avg" : 10.0, "sum" : 10.0 } } } ] } } } ``` Maybe it will also help somebody else in the future. Thanks again and best regards. Andy ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 20, 2020, 10:06am UTC](https://discuss.elastic.co/t/terms-aggregation-on-a-nested-field-for-calculating-stats-based-on-a-parent-field/237932/3 "2020-07-20T10:06:47Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.