# Get last document string value in each bucket

**URL:** <https://discuss.elastic.co/t/get-last-document-string-value-in-each-bucket/260013>\
**Category:** Elasticsearch\
**Created:** [January 2, 2021, 11:33am UTC](https://discuss.elastic.co/t/get-last-document-string-value-in-each-bucket/260013 "2021-01-02T11:33:50Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Metheny](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/metheny/32/102517_2.png) [@Metheny](https://discuss.elastic.co/u/Metheny)\
**Post date:** [January 2, 2021, 11:33am UTC](https://discuss.elastic.co/t/get-last-document-string-value-in-each-bucket/260013/1 "2021-01-02T11:33:51Z")

</div>

I have a reports index and I need to count the number of reports per-user that have a specific string (keyword) field value in their **last** document (sorted by time) in some time range.

I tried bucketing by user -\> top (1) hits -\> get max value -\> filter by max value -\> count buckets.  
It's supposed to be something like this, but this only works if the field is numeric (the "max" aggregation doesn't work on string values).  
Basically I need to extract the single string value from the top hit in order to filter the buckets.  
How can this be achieved?

```auto
    POST /reports/_search
    {
         "size": 0,
         "query": {
             "bool": {
                 "must": {
                     "range": {
                         "timestamp": {
                             "gte": "2020-12-01T21:00:00.000Z",
                             "lte": "2020-12-31T22:41:35.092Z",
                             "format": "strict_date_optional_time"
                         }
                     }
                 }
             }
         },
         "aggs": {
             "distinct_users": {
                 "terms": {
                     "field": "userId",
                     "size": 10000
                 },
                 "aggs": {
                     "top_report": { // get last document by date
                         "top_hits": {
                             "size": 1,
                             "sort": [
                                 {
                                     "timestamp": "desc"
                                 }
                             ],
                             "_source": "some_report_string_field"
                         }
                     },
                    "max_value": { // get field value of last document
                        "max": {
                            "field": "some_report_string_field"
                        }
                    },
                    "value_selector": { // select only users with a specific field value in their last document
                        "bucket_selector": {
                         "buckets_path": {
                             "max": "max_value.value"
                         },
                         "script": "params.max == 'some_value'"
                     }
                 }
             }
         },
         "users_count": { // count the filtered users
             "stats_bucket": {
                 "buckets_path": "distinct_users._count"
             }
         }
     }
}

```

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [January 2, 2021, 4:43pm UTC](https://discuss.elastic.co/t/get-last-document-string-value-in-each-bucket/260013/2 "2021-01-02T16:43:27Z")

</div>

Sounds like a “last known state” type of problem.  
This is normally best solved by building an entity-centric index from your log index. This can be done using the transform api and requires [some scripting](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-painless-examples.html#painless-top-hits) to record the last known state for each entity.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 30, 2021, 4:43pm UTC](https://discuss.elastic.co/t/get-last-document-string-value-in-each-bucket/260013/3 "2021-01-30T16:43:35Z")

</div>

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