Sum of duration field of max per group in Elasticsearch

Elastic Stack Elasticsearch
21

Hi @stephenb

I don't see Rank by Custom option when attempting to add row. Is it due to v7.17.0? In this case, is there any alternative?

I'm still confused, where should this query be used (besides verifying the results on Dev Tools) to create the table visualizer.

GET discuss-duration/_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"
              }
            }
          }
        }
      }
    }
  }
}
22

Hi @carollyl

That functionality must have come out in the 8.x versions at some point. I do not think there is an alternative to that and no, it won't be added. 7.17 is only getting minor bug fix patches and security patches.

Perhaps there's some confusion.

Visualizations do not work off a custom DSL query they can only be built through the visualization.

If you want that exact visualization, I think you're going to have to use something like Vega but again I do not have experience with that and I can't help.

If you take the query I showed you and perhaps open a topic with help me build a Vega visualization. Perhaps someone else can help.

Another possibility is to look at the transform functionality and see if you can get the data you want and do that form and then build a more simple visualization on top of it.

23

Hi @stephenb , Thank you so much for your help on the query. I managed to use Vega creating bar chart for the duration per workflow based on max id.