Efficient aggregation of counting nested field values

Like so many others I run into the well-known "too_many_buckets_exception". I am creating a data table visualization which sorts my data over four different fields, and then finally does a count over a fifth field.

A picture is worth a thousand words ...

datatable_sketch
Because I do a nested aggregation first over my four fields, and then a combined aggregation of the results, I will quickly get too many buckets. Is there a more efficient way to aggregate my results to create the table I want?

I think the biggest complication is that the final two fields are both date fields ...

I was unable to find sufficient documentation for Data Table to suit my needs, but you might try a solution that seemingly worked for me. To wit, use the "Panel Option" -> DateTimeRange -> "Entire Time Range" to remove that error.

My hypothesis is that the other choice ("Last Value") uses an outer bucket by timestamp which doesn't work if you have a lot timestamps.

And @jportner suggested in another topic that he would pass along my request for improved documentation and more examples for TSVB Data Table. :slight_smile:

Thanks for the suggestions @anelson-edge, I however do not see the options you are referring to. Are we using different elastic stack versions? Mine is 7.4.2.

Also I do not think it will help in my case. I assume what you mean is to not aggregate over the date field, but combine all data there into one bucket. That is however not what I want, I want to be able to aggregate with separate buckets for both my date fields.

I'm using 7.4.2 as well.
I haven't had a chance to think about your example, but with the most cursory amount of thinking, I would start with doing fields 1-4 and making sure that works as intended. You can then do sibling aggs of 1-4 in your 5th column and then do a bucket agg on the values of those 4 fields.

Perhaps a small dataset with expected results using direct ES queries and then asking how to visualize would also help.