# Sum of a field in a parent node and grouping by a field in a child node **URL:** \ **Category:** Elasticsearch\ **Created:** [February 8, 2023, 1:43am UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948 "2023-02-08T01:43:22Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\ **Post date:** [February 8, 2023, 1:43am UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948/1 "2023-02-08T01:43:22Z")
I'm trying to get the sum of a field in a parent node while aggregating on a field of a child node. For example, this is what I've setup: ```auto PUT order POST order/_mapping { "properties": { "order_items": { "type": "nested", "properties": { "product_id": { "type": "long" }, "product": { "type": "nested", "properties": { "manufacturer" : { "type": "nested", "properties": { "name": { "type": "keyword" } } }, "name": { "type": "keyword" }, "price": { "type": "long" } } } } } } } POST order/_bulk {"index":{}} {"order_items":[{"product":{"name":"book","price":10, "manufacturer": {"name": "alpha"}}},{"product":{"name":"pencil","price":1, "manufacturer": {"name": "alpha"}}}]} {"index":{}} {"order_items":[{"product":{"name":"pen","price":5, "manufacturer": {"name": "beta"}}},{"product":{"name":"eraser","price":2, "manufacturer": {"name": "alpha"}}}]} ``` I want to sum all the product prices and group them by the manufacturer name. So the final result should be something like: ```auto Manufacturer: Alpha Sum Price: 13 (because 10 + 1 + 2) Manufacturer: Beta Sum Price: 5 (because only one instance with 5) ``` I tried this query: ```auto GET order/_search { "size": 0, "aggs": { "manufacturerpath": { "nested": { "path": "order_items.product.manufacturer" }, "aggs": { "manufacturer": { "terms": { "field": "order_items.product.manufacturer.name" }, "aggs": { "productpath": { "nested": { "path": "order_items.product" }, "aggs": { "sum_price": { "sum": { "field": "order_items.product.price" } } } } } } } } } } ``` But it gave the result: ```auto { "aggregations": { "manufacturerpath": { "doc_count": 4, "manufacturer": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "alpha", "doc_count": 3, "productpath": { "doc_count": 2, "sum_price": { "value": 15 } } }, { "key": "beta", "doc_count": 1, "productpath": { "doc_count": 1, "sum_price": { "value": 1 } } } ] } } } } ``` Meaning `alpha has 15` and `beta has 1`. Can someone tell me what I did wrong? ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [February 8, 2023, 3:06am UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948/2 "2023-02-08T03:06:02Z")
Hi @learningelastic Looking at the indexed documents, does it make sense for Product and Manufacturer to be "nested"? If you make them objects you will get the desired answer. ---
**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\ **Post date:** [February 8, 2023, 3:14am UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948/3 "2023-02-08T03:14:19Z")
I'm not sure if I needed to apply the `type: nested` to the `product` or `manufacturer`. I tried quite a few permutations and I'm getting lost because I don't yet understand the fundamentals of summing on a field in a parent node when i want to group by a field of a child element. And assuming the parent node is within an array. ---
**Author:** ![RabBit\_BR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rabbit_br/32/82261_2.png) [@RabBit\_BR](https://discuss.elastic.co/u/RabBit_BR)\ **Post date:** [February 8, 2023, 3:18am UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948/4 "2023-02-08T03:18:31Z")
I did some changes. Look: ```auto PUT order POST order/_mapping { "properties": { "order_items": { "type": "nested", "properties": { "product_id": { "type": "long" }, "product": { "properties": { "manufacturer": { "properties": { "name": { "type": "keyword" } } }, "name": { "type": "keyword" }, "price": { "type": "long" } } } } } } } ``` Documents ```auto POST order/_doc { "order_items": [ { "product": { "name": "pen", "price": 5, "manufacturer": { "name": "beta" } } }, { "product": { "name": "eraser", "price": 2, "manufacturer": { "name": "alpha" } } } ] } POST order/_doc { "order_items": [ { "product": { "name": "book", "price": 10, "manufacturer": { "name": "alpha" } } }, { "product": { "name": "pencil", "price": 1, "manufacturer": { "name": "alpha" } } } ] } ``` Query ```auto GET order/_search { "size": 0, "aggs": { "manufacturerpath": { "nested": { "path": "order_items" }, "aggs": { "manufacturer": { "terms": { "field": "order_items.product.manufacturer.name" }, "aggs": { "sum_price": { "sum": { "field": "order_items.product.price" } } } } } } } } ``` Output: ```auto { "took": 1, "timed_out": false, "_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": { "value": 2, "relation": "eq" }, "max_score": null, "hits": [] }, "aggregations": { "manufacturerpath": { "doc_count": 4, "manufacturer": { "doc_count_error_upper_bound": 0, "sum_other_doc_count": 0, "buckets": [ { "key": "alpha", "doc_count": 3, "sum_price": { "value": 13 } }, { "key": "beta", "doc_count": 1, "sum_price": { "value": 5 } } ] } } } } ``` ---
**Author:** ![learningelastic](https://avatars.discourse-cdn.com/v4/letter/l/958977/32.png) [@learningelastic](https://discuss.elastic.co/u/learningelastic)\ **Post date:** [February 8, 2023, 1:46pm UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948/5 "2023-02-08T13:46:55Z")
Your answer worked! And I have a related question on this page here in case you know the answer. Thank you in advance! > [@These two nested aggregations produce the same result, but is one approach better than the other?](https://discuss.elastic.co/t/these-two-nested-aggregations-produce-the-same-result-but-is-one-approach-better-than-the-other/324941): > > I have two scenarios that produce identical aggregation results in the final GET order/\_search query. The only difference between the two scenarios is where I choose to declare the type: nested in the mapping and what I specify in the nested.path: in the query. SCENARIO 1 - Declare nested for order\_items PUT order POST order/\_mapping { "properties": { "order\_items": { "type": "nested", "properties": { "product\_id": { "type": "long" }, "pro… ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [March 8, 2023, 1:47pm UTC](https://discuss.elastic.co/t/sum-of-a-field-in-a-parent-node-and-grouping-by-a-field-in-a-child-node/324948/6 "2023-03-08T13:47:26Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.