Elastic(search): Get docs with max and min timestamp values

Question

I got a problem with a search I just can't figure out how to do it. My docs are of the following form:

{
"timestamp":"2015-03-17T15:05:04.563Z",
"session_id":"1",
"user_id":"jan"
}

Let's say the first timestamp of a session id is the "Login" and the last timestamp is the "Logout". I want to have all "login" and "logout" docs for all sessions (if possible sorted by user_id). I managed to get the right timestamps with aggregations:

{
"aggs" : {
    "group_by_uid" : {
        "terms" : { 
            "field" : "user_id"
        },
        "aggs" : {
            "group_by_sid" : {
                "terms" : {
                    "field" : "session_id"
                },
                "aggs" : {
                    "max_date" : {
                        "max": { "field" : "timestamp" }
                    },
                    "min_date" : {
                        "min": { "field" : "timestamp" }
                    }
                }
            }
        }
    }
}
}

But how do I get the corresponding docs? I also don't mind if i have to do 2 searches (one for the logins and one for the logouts). I tried tome top hits aggregations and sorting stuff but I always get parse errors :/

I hope someone can give me a hint :)

Best regards, Jan

Answer 1

Here's a solution in a single search based on the approach proposed by Sloan Ahrens. The advantage is that the start and end session entries are in the same bucket.

{
"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" } } ]
                 }
              }
           }
        }
     }
  }
}
}

Cheers, Jan