Search code examples
elasticsearch

sum of duration field of max per group in Elasticsearch

I would like to create a visualizer by summing up duration field after retrieving max id per group in Elasticsearch. For example:

Data is:

id workflow sid duration
1 A x1 1m
1 A x2 2m
2 A x1 2m
2 A x2 3m
1 B y1 1m
1 B y2 2m
2 B y1 2m
2 B y2 3m
3 B y1 4m
3 B y2 2m

Given the table below, expected returned data as follows, which is max of id per workflow and sum up the duration.

id workflow total
2 A 5m
3 B 6m

I'm new to Elasticsearch query and Kibana. Appreciate it if you can provide a pointer how to resolve my problem statement.

{
  "size": 0,
  "aggs": {
    "my-bucket": {
      "terms": {
        "field": "workflow"
      },
      "aggs": {
        "max_id": {
          "max": {
            "field": "id"
          }
        }
      }
    }
  }
}

I have the search query above with expected bucket of workflow and max id #. How to use the max id # to retrieve the sid and sum up the duration.

Solution

  • This is another approach that I have learned from Elastic Stack community.

    GET test/_search
{
  "size": 0,
  "aggs": {
    "workflow": {
      "terms": {
        "field": "workflow"
      },
      "aggs": {
        "ids": {
          "terms": {
            "field": "id",
            "order": { "max_id": "desc" },
            "size": 1
          },
          "aggs": {
            "max_id": {
              "max": {
                "field": "id"
              }
            },
            "sum_duration": {
              "sum": {
                "field": "duration"
              }
            }
          }
        }
      }
    }
  }
}