Group, calculation and order of ElasticSearch data

Question

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.

Answer 1

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