Group, calculation and order of ElasticSearch data

I have a lot of data stored in the following format (I simplified the data to explain the problem).

What I need is:

group all the data by "Action Id" field
calculate the difference between max and min values of "Created Time" for each group (from the previous action)
order the results by the calculated field ("Action duration" - difference between max and min)

I use NEST (C#) to query the ElasticSearch. I think that if you can help me with native Elastic query it also will be very helpful, I'll translate it to C#.

Thank you.

Solution

Case your mappings looks like that:

PUT /index
{
  "mappings": {
    "doc": {
      "properties": {
        "ActionId": {
          "type": "text",
          "fielddata": true
        },
        "CreatedDate":{
          "type": "date"
        },
        "SubActionName":{
          "type": "text",
          "fielddata": true
        }
      }
    }
  }
}

Your elasticsearch query should look like that:

GET index/_search
{
  "size": 0,
  "aggs": {

    "actions": {
      "terms": {
        "field": "ActionId"
      },
      "aggs": {
        "date_created": {
          "date_histogram": {
            "field": "CreatedDate",
            "interval": "hour"
          },
          "aggs": {
            "the_max": {
              "max": {
                "field": "CreatedDate"
              }
            },
            "the_min": {
              "min": {
                "field": "CreatedDate"
              }
            },
            "diff_max_min": {
              "bucket_script": {
                "buckets_path": {
                  "max": "the_max",
                  "min": "the_min"
                },
                "script": "params.max - params.min"
              }
            }

          }
        }
      }
    }
  }
}

You can read more about Pipeline Aggregetions here

Hope that helps