Metric Visualization: Average Bucket over all the terms of an aggregation

[Rami_Batal]

Mapping

"mappings":{
	"user_stats": {
	   "properties": {
	        "daily_stats": {
	          "type": "object"
	        }
	   }
	}
}

• The _id of each documents represent a user ID.
• The daily_stats is an array of objects, each object represents a day and contains the count of two types of actions.

Example

Here is an example for the user ID 1063:

{
        "_index" : "user_stats",
        "_type" : "user_stats",
        "_id" : "1063",
        "_score" : 1.0,
        "_source" : {
            "daily_stats" : [
                {
                   "action_1_count" : 4,
                   "action_2_count" : 10
                },
                {
                   "action_1_count" : 1,
                   "action_2_count" : 3
                },
                {
                   "action_1_count" : 3,
                   "action_2_count" : 2
                }
          ]
}

Goal

I have several 100Ks of users in my index, and my goal is to create a Metric Visualization in Kibana that shows the overall average of action_1_count. This means that I have to:

1. Calculate the average of action_1_count for each user, over all his/her daily_stats objects.
2. Calculate the average of the averages of all users.

Problem

• I am trying to do an Average Bucket and choose the Terms Aggregation for the _id field, but I am obliged to fix the size of the aggregation.
• As I said, my goal is to calculate the average over all user, so I don't want to fix the size parameter.

Questions

• Is there a way to bypass the the need to fix the size of the aggregation in Kibana.
• If this is not possible, is there an alternative solution to acheive the same goal?

[tsullivan (Tim Sullivan)]

@Rami_Batal

The daily_stats is an array of objects, each object represents a day and contains the count of two types of actions.

To search this data correctly with Elasticsearch, it will need not object mapping but nested: Nested field type | Elasticsearch Guide [8.11] | Elastic.

Unfortunately, Kibana currently does not support data mapped as nested. You'll need to have fields that summarize the nested data with metrics, and use those fields to aggregate on.