Sum bucket aggregation cannot sum more than one level deep

[andrewvc]

Executing a sum_bucket aggregation with a bucket path multiple levels deep, like bucket_1>bucket_2>metric will fail with:

{
  "error": {
    "root_cause": [],
    "type": "search_phase_execution_exception",
    "reason": "",
    "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
}

It would be preferable that this work. You can work around this by having one sum_bucket per aggregation depth level, but it would be nice to either have this return a more relevant error or just work.

[andrewvc]

After discussing this with @polyfractal it seems that this is most likely only the case with nested terms aggregations.

[colings86]

This will be true for any nested aggregations (e.g. terms>histogram) and was made like this by design. I agree that we should improve the error message here but I am hesitant to support summing over multiple levels as its possible to do (like you say) with multiple sum_bucket aggregations and we don't currently have support int eh bucket_path syntax for traversing multi bucket aggregations

[andrewvc]

@colings86 I think that makes sense. I do think it'd be better to return a 422 response code than a 503, along with a more helpful error.

[andrewvc]

I'll just say that I was confused by this for a while till I did some experiments and determined the workaround.

[polyfractal]

@elastic/es-search-aggs

Note: keeping this open because we should fix the error message to be more helpful (display the name of the path component that was not compatible), not the underlying issue of pathing through a multi-bucket.

[ssmulders]

After a lot of digging I came here as well, to find it's not a bug but a feature. The error message threw me off as well.

I was trying to do a extended_stats_bucket on a result that was first bucketed by modelId, and then another layer that bucketed the results in 24 hour chunks with date_histogram, which had an agg that summed up a specific value in that 24 hour window.

This produced a result like this, that I was unable to unleash the stats on:

  "aggregations" : {
    "models" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : 1602,
          "doc_count" : 116,
          "24_hour_windows" : {
            "buckets" : [
              {
                "key_as_string" : "2019-03-01T00:00:00.000Z",
                "key" : 1551398400000,
                "doc_count" : 11,
                "value" : {
                  "value" : 39.0
                }
              },
              {
                "key_as_string" : "2019-03-02T00:00:00.000Z",
                "key" : 1551484800000,
                "doc_count" : 18,
                "value" : {
                  "value" : 59.0
                }
              },

Erroring out with:

{
  "error" : {
    "root_cause" : [ ],
    "type" : "search_phase_execution_exception",
    "reason" : "",
    "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: [Object[]] at aggregation [24_hour_windows]"
    }
  },
  "status" : 500
}

I would have expected models>24_hour_windows>value to work.

[jaymc-arg]

@ssmulders so how did you solve it? i don't get what feature you are talking about.