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

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.

Metric Visualization in Kibana

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.

In Kibana Metric Visualization, we have to set the size of the term aggregation, but we need all the terms to calculate the metric.

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?

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.

1 Like

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.