Hi,
I have event data like this. And I need to calculate aggs at multiple levels of bucket aggregations. I cannot find a way to calculate aggs except at the "leaf level".
{ "id": 1, "field1" : "A1", "field2" : "B11", "field3" : 100, "field4" : 2.0}
{ "id": 2, "field1" : "A1", "field2" : "B21", "field3" : 95, "field4" : 1.8}
{ "id": 3, "field1" : "A1", "field2" : "B11", "field3" : 124, "field4" : 1.6}
{ "id": 4, "field1" : "A2", "field2" : "B12", "field3" : 104, "field4" : 2.1}
{ "id": 5, "field1" : "A2", "field2" : "B22", "field3" : 101, "field4" : 1.7}
{ "id": 6, "field1" : "A2", "field2" : "B22", "field3" : 103, "field4" : 1.4}
In my data set,

field3 and field4 can be any numeric value and the values vary with each event.

field1 and field2 are used as terms in query.

A1 has multiple Bx1 terms. Similarly, A2 has multiple Bx2 terms.

I'm interested in averaging fields3 and fields4 by buckets of Bxy terms. I would get 1 avg for each Bxy term (level2 avg).
e.g id 5, 6 would become one level2 avg (based on A2, B22 term combination). Similarly, id 1, 3 would become one level2 avg (based on A1, B11 term combination)
NOTE: I am able to use nested terms aggregations and calculate this level2 avg.
 Then I need a rollup avg for A1 (level1), that is an avg of the avg calculated at level2.
I'm unable to figure out how to do this rollup avg. I tried using bucket_path, but get "Found two sub aggregation definitions" error as I have a terms and avg aggregation at level1.
 If I were successful, I would calculate another rollup avg for all Ax (level0), that is an avg of all level1 avg computed.
How can I do this in ES?
Thanks.
The working level2 avg query is below. The attempt at using bucket_path is further below.
GET _search
{
"query": {
"match_all": {}
},
"aggs": {
"by_time": {
"date_histogram": {
"field": "@timestamp",
"interval": "day"
},
"aggs": {
"field1_aggs": {
"terms": {
"field": "field1.raw",
"size": 0
},
"aggs": {
"field2_aggs": {
"terms": {
"field": "field2.raw",
"size": 0
},
"aggs": {
"field3_avg": {
"avg": {
"field": "field3"
}
}
}
}
}
}
}
}
}
}
Attempt with bucket_path
GET _search
{
"query": {
"match_all": {}
},
"aggs": {
"by_time": {
"date_histogram": {
"field": "@timestamp",
"interval": "day"
},
"aggs": {
"field1_aggs": {
"terms": {
"field": "field1.raw",
"size": 0
},
"aggs": {
"field2_aggs": {
"terms": {
"field": "field2.raw",
"size": 0
},
"aggs": {
"field3_avg": {
"avg": {
"field": "field3"
}
}
}
}
},
"aggs": {
"field1_avg": {
"avg": {
"buckets_path": { "field2_aggs>field3_avg.avg" }
}
}
}
}
}
}
}
}