Barplot of aggregation on result of aggregation

[denismaciel (D Pinto)]

I have the following documents in Elasticsearch. Each document represents an order from a user of an e-commerce shop. Each document has the items that were bought (in an array) together with the user id.

I want to calculate the average number of items per order for each user, count how many users have X orders and then plot it in a bar plot or a histogram.

More details below:

{
"user_id":  1,
"bought_items" : ["Ball", "Pen"]
},
{
"user_id":  2,
"bought_items" : ["SomeItem1", "SomeItem2", "SomeItem3", "SomeItem4"]
},
{
"user_id":  1,
"bought_items" : ["Car", "Motorcycle", "Truck", "Yacht"]
},
{
"user_id": 3,
"bought_items": ["Iterm1", "Item2", "Item3"]
}

First, I want to per user get the average items in an order. Here:

user 1 => 3
user 2 => 4
user 3 => 3

And then make a bar plot (or a histogram) where the bars represents how many users have on average bought X items. Here it would be:

2 users have bought 3 items on average.
1 user has bought 4 items on average.

I imagine the result of the query would look like something like this:

[{
"item_quantity": 3,
"count" : 2
},
{
"item_quantity": 4,
"count" : 
}]

How could I accomplish that?

[lukas (Lukas Olson)]

Arrays don't work well with Kibana's visualization abilities. If you want to do something like that you'd first have to also index item_count in each document.

Even then, I'm not sure this will really work how you want, because the average items in an order isn't discrete, like you've suggested. For example, if user 1 had only three items in the second order, then their average items in an order would be 2.5. Would you be wanting to plot that as part of item_quantity 2 or 3? Or separately?

The closest you can probably get is to have a metric that shows you the overall average of average order size per user. To do this, you would use an "average bucket" aggregation where you bucket by "terms for user_id" and select as the metric the "average item_count".