Using Top Hits aggregation for calculating time duration in each grouped field

Elastic Stack Elasticsearch
1

I am trying to get time duration between each grouped field.

Example:

My document is shown below:

{
"timestamp":"2017-07-31T15:05:04.563Z",
"session_id":"1",
"user_id":"jenny"
}

I want to get time duration in each session_id.

Moreover, I got a reference of searching solution from stackoverflow(http://bit.ly/2vaBHHj) shown below:

{
"aggs": {
  "group_by_uid": {
     "terms": {
        "field": "user_id"
     },
     "aggs": {
        "group_by_sid": {
           "terms": {
              "field": "session_id"
           },
           "aggs": {
              "session_start": {
                 "top_hits": {
                    "size": 1,
                    "sort": [ { "timestamp": { "order": "asc" } } ]
                 }
              },
              "session_end": {
                 "top_hits": {
                    "size": 1,
                    "sort": [ { "timestamp": { "order": "desc" } } ]
                 }
              }
           }
        }
     }
  }
}
}

My question is that how can I get the time duration (session_end.timestamp - session_start.timestamp )?
Can anybody help me ? Thank you!

2

Attempting this sort of computation on a large event store with many unique session IDs is not advised.
See entity centric indexing

3

You can simplify things by using a stats aggregation on the inner timestamp
field. You will still need to do the final calculation on the client side,
but I rather have such logic outside of the database anyways.

https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-stats-aggregation.html

4

Thanks for your sharing!

5

Hi Ivan,
Do you know is there a way to calculate time duration in this query? How can I pick session_start.timestamp and session_end.timestamp to calculate their difference?
Or I can only do calculation after the getting the result.

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.