# Aggregate the total sum of aggregation field (bucket\_path)

**URL:** <https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364>\
**Category:** Elasticsearch\
**Created:** [June 30, 2016, 6:39am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364 "2016-06-30T06:39:46Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [June 30, 2016, 6:39am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/1 "2016-06-30T06:39:46Z")

</div>

Hi all, I'm trying with no luck so far, to aggregate the total impressions sum of impressions field, but I keep getting an error. I got the following query:

```auto
GET smarttag-2016.06.28.*/_search?search_type=count
{
  "query": {
    "bool": {
      "must": [{
        "range": {
          "@timestamp": {
            "gte": "2016-06-28T10:00:00",
            "lt": "2016-06-28T11:00:00"
          }
        }
      }],
      "must_not": [
        {
          "term": {
            "tagType": {
              "value": "app"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "TagId": {
      "terms": {
        "field": "TagId",
        "size": 0
      },
      "aggs": {
        "name": {
          "terms": {
            "field": "url",
            "size": 0
          },
          "aggs": {
            "tagType": {
              "terms": {
                "field": "type"
              },
              "aggs": {
                "impressions": {
                  "sum": {
                    "field": "imp"
                  }
                }
              }
            }
          } 
        }
      }
    },
    "sum_imp": {
      "sum_bucket": {
          "buckets_path": "TagId>name>tagType>impressions"
          }
      }
  }
}
```

The error:

```auto
   {
       "error": {
          "root_cause": [],
          "type": "reduce_search_phase_exception",
          "reason": "[reduce] ",
          "phase": "query",
          "grouped": true,
          "failed_shards": [],
          "caused_by": {
             "type": "aggregation_execution_exception",
             "reason": "buckets_path must reference either a number value or a single value numeric metric aggregation, got: java.lang.Object[]"
          }
       },
       "status": 503
    } 
```

I don't understand what am I doing wrong.

---

<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:** [June 30, 2016, 7:56am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/2 "2016-06-30T07:56:11Z")

</div>

Pipeline aggregations cannot sum over multiple nestings of aggregations. The sum\_bucket aggregation should be used to sum a metric immediately under a sibling bucket aggregation. If you are summing the impressions over all matching documents why not use the `sum` metric aggregation instead of the `sum_bucket` pipeline aggregation?

```auto
GET smarttag-2016.06.28.*/_search?search_type=count
{
  "query": {
    "bool": {
      "must": [{
        "range": {
          "@timestamp": {
            "gte": "2016-06-28T10:00:00",
            "lt": "2016-06-28T11:00:00"
          }
        }
      }],
      "must_not": [
        {
          "term": {
            "tagType": {
              "value": "app"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "sum_imp": {
      "sum": {
          "field": "imp"
          }
      }
  }
}

```

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [June 30, 2016, 10:19am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/3 "2016-06-30T10:19:20Z")

</div>

I'm getting an error when I try to place it like that:

```auto

```

If I try to place it before the aggs, I'm getting:

```auto

```

---

<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:** [June 30, 2016, 10:27am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/4 "2016-06-30T10:27:22Z")

</div>

Can you paste the complete request you are trying?

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [June 30, 2016, 10:44am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/5 "2016-06-30T10:44:10Z")

</div>

```auto
 GET smart-2016.06.29.*/_search?search_type=count
{
  "query": {
    "bool": {
      "must": [{
        "range": {
          "@timestamp": {
            "gte": "2016-06-28T10:00:00",
            "lt": "2016-06-29T11:00:00"
          }
        }
      }],
      "must_not": [
        {
          "term": {
            "tagType": {
              "value": "vast-inapp"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "cTagId": {
      "terms": {
        "field": "cTagId",
        "size": 0
      },
      "aggs": {
        "mTagId": {
          "terms": {
            "field": "mTagId",
            "size": 0
      },
      "aggs": {
        "name": {
          "terms": {
            "field": "url",
            "size": 0
          },
          "aggs": {
            "tagType": {
              "terms": {
                "field": "tagType"
              },
              "aggs": {
                "impressions": {
                  "sum": {
                    "field": "impressions"
                      }
                    }
                  }
                }
              } 
            }
          }
        }
      }
    }
  },
    "aggs": {
    "sum_impressions": {
      "sum": {
          "field": "impressions"
          }
      }
  }
}

```

---

<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:** [June 30, 2016, 10:47am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/6 "2016-06-30T10:47:37Z")

</div>

You need to remove the repeated aggs object in your request so it is as the below request. Also you should not use `size: 0` on the terms aggregation especially on high cardinality fields (such as your url field) dues to the reasons detailed in this issue: [https://github.com/elastic/elasticsearch/issues/18838](https://github.com/elastic/elasticsearch/issues/18838)

```auto
GET smart-2016.06.29.*/_search?search_type=count
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "@timestamp": {
              "gte": "2016-06-28T10:00:00",
              "lt": "2016-06-29T11:00:00"
            }
          }
        }
      ],
      "must_not": [
        {
          "term": {
            "tagType": {
              "value": "vast-inapp"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "cTagId": {
      "terms": {
        "field": "cTagId",
        "size": 0
      },
      "aggs": {
        "mTagId": {
          "terms": {
            "field": "mTagId",
            "size": 0
          },
          "aggs": {
            "name": {
              "terms": {
                "field": "url",
                "size": 0
              },
              "aggs": {
                "tagType": {
                  "terms": {
                    "field": "tagType"
                  },
                  "aggs": {
                    "impressions": {
                      "sum": {
                        "field": "impressions"
                      }
                    }
                  }
                }
              }
            }
          }
        }
      }
    },
    "sum_impressions": {
      "sum": {
        "field": "impressions"
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [June 30, 2016, 11:01am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/7 "2016-06-30T11:01:43Z")

</div>

When I remove the 'aggs' object I'm getting:

```auto

```

---

<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:** [June 30, 2016, 11:16am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/8 "2016-06-30T11:16:35Z")

</div>

I've updated my previous post to correct the placement of braces.

---

<div class="post-metadata">

**Author:** ![NativAt](https://avatars.discourse-cdn.com/v4/letter/n/fbc32d/32.png) [@NativAt](https://discuss.elastic.co/u/NativAt)\
**Post date:** [July 4, 2016, 8:51am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/9 "2016-07-04T08:51:36Z")

</div>

Thank you! It's working now!

---

<div class="post-metadata">

**Author:** ![Ruslan\_Didyk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ruslan_didyk/32/13967_2.png) [@Ruslan\_Didyk](https://discuss.elastic.co/u/Ruslan_Didyk)\
**Post date:** [December 19, 2016, 9:41am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/10 "2016-12-19T09:41:49Z")

</div>

Hi all, I'm getting same error when I'm trying to use Pipeline Aggregation with 'terms' aggregation deeper that one level.  
Here is my simple mappings: (I use Elasticsearch 5.1.1)

> ```
> PUT author
> {
> "mappings": {
> "author" : {
> "properties": {
> "name" : {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }                         
> }                    
> },
> "gender" : {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "age" : {"type": "integer"}
> }   
> },
> "book" : {
> "properties": {
> "title" : {
> "type": "text",
> "fields": {
> "keyword": {
> "type": "keyword",
> "ignore_above": 256
> }
> }
> },
> "pages" : {"type" : "integer"}
> },
> "_parent" : {"type": "author"}
> }
> }
> }
> 
> ```

Query below with one 'terms' aggregation works fine:

GET author/author/\_search

> ```
> {
> "size": 0,
> "aggs" : {
> "gender" : {
> "terms" : {
> "field" : "gender.keyword"
> },
> "aggs" : {
> "avg" : {
> "avg" : {
> "field" : "age"
> }
> }
> }
> },
> "max" : {
> "max_bucket" : {
> "buckets_path" : "gender>avg"
> }
> }
> }
> }
> 
> ```

But when query has two or more 'terms' in fails with an error. Query is bellow:

GET author/author/\_search

> ```
> {
> "size":0,
> "aggs":{
> "gender":{
> "terms":{
> "field":"gender.keyword"
> },
> "aggs":{
> "age":{
> "terms":{
> "field":"age"
> },
> "aggs":{
> "avg":{
> "avg":{
> "field":"age"
> }
> }
> }
> }
> }
> },
> "max":{
> "max_bucket":{
> "buckets_path":"gender>age>avg"
> }
> }
> }
> }
> 
> ```

Error message:

> ```
> {
> "error": {
> "root_cause": [],
> "type": "reduce_search_phase_exception",
> "reason": "[reduce] ",
> "phase": "fetch",
> "grouped": true,
> "failed_shards": [],
> "caused_by": {
> "type": "aggregation_execution_exception",
> "reason": "buckets_path must reference either a number value or a single value numeric metric aggregation, got: java.lang.Object[]"
> }
> },
> "status": 503
> }
> 
> ```

Do you have any ideas how to solve this issue? Or Pipeline Aggregations do not support this case? Thank's a lot for any suggestions.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [December 19, 2016, 11:01am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/11 "2016-12-19T11:01:57Z")

</div>

This looks like a different scenario - please open a different issue where we can discuss it.  
It seems an odd request because the `terms` for "age" will only ever produce buckets that contain a single value so computing the average of this single value (and then the max of these) seems an odd request.  
Either way - open a seperate topic please if you want to discuss further.

---

<div class="post-metadata">

**Author:** ![Ruslan\_Didyk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ruslan_didyk/32/13967_2.png) [@Ruslan\_Didyk](https://discuss.elastic.co/u/Ruslan_Didyk)\
**Post date:** [December 19, 2016, 11:50am UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/12 "2016-12-19T11:50:04Z")

</div>

Thank you. I'll create a separate topic for this scenario.

---

<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, 10:04pm UTC](https://discuss.elastic.co/t/aggregate-the-total-sum-of-aggregation-field-bucket-path/54364/13 "2017-07-05T22:04:48Z")

</div>