Elasticsearch nested aggregation ratios using nested and root level data

user_2213915 0
Viewed 166

I have what feels like a simple aggregation. I have a document that's timing code like so:

{
  "task_start": "2020-06-03T21:19:07.908821Z",
  "task_end": "2020-06-03T21:27:00.323790Z",
  "sub_tasks": [
    {
      "key": "sub-task1-time-milliseconds",
      "value": 3310
    },
    {
      "key": "sub-task2-time-milliseconds",
      "value": 2410
    },
    ...
  ]
}

where sub_tasks is nested. What I'd like to get is the median ratio of time in each sub task to the entire task time. The entire task time would be just end_time - start_time. I know how to aggregate the median sub task time and total task time individually. But I'd like to aggregate the ratio per document.

The issue is on a nested aggregation I can only access data with the nested data, and within a reverse nested aggregation I can only access data at the root level, but never both together. I understand there's a way to copy_to so I have the task times in the nested path, but I don't have the ability to modify the indexing structure, and wouldn't want the extra storage either.

Here's what I've tried. For a nested aggregation:

{
  "aggs": {
    "task_metrics": {
      "nested": {
        "path": "sub_tasks"
      },
      "aggs": {
        "sub_task_metrics": {
          "filter": {
            "term": {
              "sub_tasks.key": "sub-task1-time-milliseconds"
            }
          },
          "aggs": {
            "median_time": {
              "percentiles": {
                "script": {
                  "lang": "painless",
                  "source": """
                            double task_time = (doc['task_end'].value.millis - doc['task_start'].value.millis);
                            return doc['sub_tasks.value'].value / task_time; 
                            """
                },
                "percents": 50
              }
            }
          }
        }
      }
    }
  }
}

But in that aggregation doc['task_start'] and doc['task_end'] just return zero because I don't have access to them. To get access, I also tried a reverse_nested that adds another sub aggregation. This gets me access to doc['task_start'] and doc['task_end'], but then doc['sub_tasks.value'].value just returns 0.

It just feels like this should be possible, but when I read over pipeline aggregations and other script aggregations, I don't believe any of those do what I want. Greatly appreciate any help, thank you!

elasticsearch
1 Answers

This one's tricky -- already discussed here.

I think you'll have to resort a bit of scripted_metrics and some method mocking because the exposed painless API is somewhat limited:

{
  "size": 0, 
  "aggs": {
    "task_metrics_median": {
      "scripted_metric": {
        "init_script": "state.ratios = new ArrayList();",

        "map_script": """
          // access the source incl. the nested subtasks
          def d = params._source;

          for (def subtask : d.sub_tasks) {
            // mimicking a `term` query
            if (subtask.key != 'sub-task1-time-milliseconds') break;

            // incoming as strings so parse
            def millis_end = ZonedDateTime.parse(d.task_end).toInstant().toEpochMilli();
            def millis_start = ZonedDateTime.parse(d.task_start).toInstant().toEpochMilli();

            double task_time = (millis_end - millis_start);

            // prevent zero division
            if (task_time <= 0) break;

            state['ratios'].add(subtask.value / task_time);  
          }
        """,

        "combine_script": """
            def ratios = state.ratios;
            Collections.sort(ratios);

            // trivial median calc
            double median;
            if (ratios.length % 2 == 0) {
                median = ((double)ratios[ratios.length/2] + (double)ratios[ratios.length/2 - 1])/2;
            } else {
                median = (double) ratios[ratios.length/2];
            }

            return median
        """,

        "reduce_script": "return states"
      }
    }
  }
}
user_81603180
Related