Issue with Nested Aggregation & Sum Query

Elastic Stack Elasticsearch
1

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"

2 
{
  "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

3

Hi Colings

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

4

What is the error you are getting?

5

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

6

My example was based off yours and I didn't see that you have got aggs1, aggs2and 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

7

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]]]; }]