Hello,
I'd like to create a bar chart in Kibana, bucketing with a terms aggregation and then using as metrics the average of a field X, but I'd like to filter this only for the buckets with at least 100 items, i.e., I want the average of X, sorted by X for all the buckets with at least 100 items.
To achieve the filtering, I'm using JSON input field in the buckets configuration, adding the following:
{"min_doc_count": 100}
However, since the order by is a mandatory field in the buckets configuration, I ended up generating the following query:
{ "aggs": { "2": { "terms": { "field": "city", "size": 5, "order": { "1": "desc" }, "min_doc_count": 100 }, "aggs": { "1": { "avg": { "field": "reviews.rating" } } } } },
but like this, I believe that what ES is doing is first the sorting and then trimming the first 5 results by the min_doc_count, whereas what i'd like to have is to first trim the results by min_doc_count and then sort to get the first 5.
** Example use case**
Suppose that I have a dataset of item reviews, with a category field and a rating field. I would like to create a bar chart showing the top 10 categories in terms of average review rating, but I don't want a category with only a few reviews to show up first, so I'd want to filter for cities having at least 100 reviews.
Thanks
Marco