# Issue with Nested Aggregation & Sum Query

**URL:** <https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140>\
**Category:** Elasticsearch\
**Created:** [December 2, 2015, 10:03am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140 "2015-12-02T10:03:48Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![code\_blue](https://avatars.discourse-cdn.com/v4/letter/c/7ea924/32.png) [@code\_blue](https://discuss.elastic.co/u/code_blue)\
**Post date:** [December 2, 2015, 10:03am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/1 "2015-12-02T10:03:48Z")

</div>

We have the following aggregation query:

```
{
	"aggs": {
		"STATE": {
			"terms": {
				"field": "STATE.raw"
			},
			"aggs": {
				"CITY": {
					"terms": {
						"field": "CITY.raw"
					},
					"aggs": {
						"STREET": {
							"terms": {
								"field": "STREET.raw"
							},
							"sum_agg": {
								"sum": {
									"field": "FIELD_NAME"
								}
							}
						}
					}
				}
			}
		}
	}
}

```

We have 3 levels of aggregation. We are able to find sum at the lowest bucket. But how can we find sum of a field for "aggs2" & "aggs1"

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [December 2, 2015, 10:10am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/2 "2015-12-02T10:10:42Z")

</div>

```auto
{
  "aggs1": {
    "STATE": {
      "terms": {
        "field": "STATE.raw"
      },
      "sum_agg": {
        "sum": {
          "field": "FIELD_NAME"
        }
      },
      "aggs2": {
        "CITY": {
          "terms": {
            "field": "CITY.raw",
            "size": 0
          },
          "sum_agg": {
            "sum": {
              "field": "FIELD_NAME"
            }
          },
          "aggs3": {
            "STREET": {
              "terms": {
                "field": "STREET.raw",
                "size": 0
              },
              "sum_agg": {
                "sum": {
                  "field": "FIELD_NAME"
                }
              }
            }
          }
        }
      }
    }
  }
}

```

Also, it is not a good idea to use `size: 0` with the terms aggregation. For high cardinality field (like street names) you run the risk of Out of Memory Errors occurring which will destabilise your cluster

---

<div class="post-metadata">

**Author:** ![code\_blue](https://avatars.discourse-cdn.com/v4/letter/c/7ea924/32.png) [@code\_blue](https://discuss.elastic.co/u/code_blue)\
**Post date:** [December 2, 2015, 10:21am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/3 "2015-12-02T10:21:08Z")

</div>

Hi Colings

We are getting an error when we include top hits along with the query. (Updated my query above)

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [December 2, 2015, 10:33am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/4 "2015-12-02T10:33:45Z")

</div>

What is the error you are getting?

---

<div class="post-metadata">

**Author:** ![code\_blue](https://avatars.discourse-cdn.com/v4/letter/c/7ea924/32.png) [@code\_blue](https://discuss.elastic.co/u/code_blue)\
**Post date:** [December 2, 2015, 10:37am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/5 "2015-12-02T10:37:40Z")

</div>

Parse Failure [No parser for element [aggs1]]. Also tried your query but get the same error.

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [December 2, 2015, 10:42am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/6 "2015-12-02T10:42:52Z")

</div>

My example was based off yours and I didn't see that you have got `aggs1`, `aggs2`and `aggs3` in your request. This is wrong, these should be `aggs` instead. I would suggest you read the following link to understand how to build aggregation requests correctly: [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html#search-aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html#search-aggregations)

---

<div class="post-metadata">

**Author:** ![code\_blue](https://avatars.discourse-cdn.com/v4/letter/c/7ea924/32.png) [@code\_blue](https://discuss.elastic.co/u/code_blue)\
**Post date:** [December 2, 2015, 11:00am UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/7 "2015-12-02T11:00:41Z")

</div>

Just updated the query (Number with aggs was to indicate the nested level )  
Now getting the following error:  
Parse Failure [Found two aggregation type definitions in [STREET]: [terms] and [sum\_agg]]]; }]

---

<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:** [July 5, 2017, 11:34pm UTC](https://discuss.elastic.co/t/issue-with-nested-aggregation-sum-query/36140/8 "2017-07-05T23:34:10Z")

</div>