Generate chart from custom aggregation

[oula_oula]

Hi,

I'm trying to make a chart that shows for each superhero the time he's spent between to cities.
The problem is that I cannot add a field using "scripted field" method because it cannot match multiple docs at the same time. So, I think I should use aggregations but I don't really understand how it works for generating charts.

Actually, my goal is to join two docs by superhero "name" fields and make a duration by calculating the time difference between "arrival" fields of each document of the chosen superhero.
At the end I would like to have a chart gathering all superheros (or filtered ones) times duration calculated for disclosing who is lingering at the bar.

Thank you

#Doc A
{
        "_index" : "test_inds",
        "_type" : "_doc",
        "_score" : 1.0,
        "_source" : {
          "name" : """Iron Man""",
          "arrival" : "2020-01-01T06:20:00.000Z",
          "city" : "New-York",
          "@timestamp" : "2020-04-01T14:11:58.536Z",
          "loglvl" : "INFO"
        }
      },
#Doc B
      {
        "_index" : "test_inds",
        "_type" : "_doc",
        "_score" : 1.0,
        "_source" : {
          "name" : """Iron Man""",
          "arrival" : "2020-01-03T10:50:00.000Z",
          "city" : "Los Angeles",
          "@timestamp" : "2020-04-01T14:11:58.536Z",
          "loglvl" : "INFO"
        }

[matw (Matthias Wilhelm)]

Hi @oula_oula

This is not possible with scripted fields, you would need have this pre-aggregated, so you'd need 1 record with timestamp start, end, duration to make it work.
It might be possible to do with the Vega-Lite visualization of Kibana. It supports aggregations and calculations with the given records

Aggregation

Calculate Transform

Best,
Matthias