# Aggregation query: get value from the latest doc

**URL:** <https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795>\
**Category:** Elasticsearch\
**Created:** [July 19, 2018, 7:15pm UTC](https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795 "2018-07-19T19:15:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jimyoo](https://avatars.discourse-cdn.com/v4/letter/j/53a042/32.png) [@jimyoo](https://discuss.elastic.co/u/jimyoo)\
**Post date:** [July 19, 2018, 7:15pm UTC](https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795/1 "2018-07-19T19:15:07Z")

</div>

I want to build a query to get the price from the latest document (time). And within the query, there can be other aggregations like “avg”.  
The query will be like:

> ```
> "query" : {
> // some filters
> "aggregations" : {
> "AVG_price” : {
> "avg" : {
> "field" : “price”
> }
> },
> "LAST_price” : {
> // get the price from the latest, i.e, "price" from the doc of max(“time”); how??
> }
> // snip   
> 
> ```

Documents have fields like:

> “price” : 100  
> “time” (epoch) : 1531332965000

What will be the best way to have “Last\_price”?  
We are using Elasitcsearch 5.6.4.  
Thanks in advance.

---

<div class="post-metadata">

**Author:** ![jimyoo](https://avatars.discourse-cdn.com/v4/letter/j/53a042/32.png) [@jimyoo](https://discuss.elastic.co/u/jimyoo)\
**Post date:** [July 20, 2018, 5:49pm UTC](https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795/2 "2018-07-20T17:49:30Z")

</div>

Solved with a script. Let me know if you can think of a better solution (especially performance wise).

```
    "LAST_Price" : {
      "scripted_metric" : {
        "init_script" : {
          "source" : "params._agg.x=0; params._agg.y=\"\";",
          "lang" : "painless"
        },
        "map_script" : {
          "source" : "if (params['_source']['price'] != null) {if (params['_source']['time'] > params._agg.x) {params._agg.x=params['_source']['time'];params._agg.y=params['_source']['price'] }}",
          "lang" : "painless"
        },
        "combine_script" : {
          "source" : "return params._agg",
          "lang" : "painless"
        },
        "reduce_script" : {
          "source" : "long xx=0; String yy=\"\";for (tt in params._aggs) {if (tt.x>xx) {xx=tt.x; yy=tt.y}}return yy",
          "lang" : "painless"
        }
      }
    }
```

---

<div class="post-metadata">

**Author:** ![Johnnycc1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/johnnycc1/32/26069_2.png) [@Johnnycc1](https://discuss.elastic.co/u/Johnnycc1)\
**Post date:** [July 22, 2018, 5:49am UTC](https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795/3 "2018-07-22T05:49:24Z")

</div>

What about using a nested top\_hits aggregation to return 1 document sorting by age descending.

---

<div class="post-metadata">

**Author:** ![jimyoo](https://avatars.discourse-cdn.com/v4/letter/j/53a042/32.png) [@jimyoo](https://discuss.elastic.co/u/jimyoo)\
**Post date:** [July 23, 2018, 7:07pm UTC](https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795/4 "2018-07-23T19:07:45Z")

</div>

Top\_hits works for me; even though I need to work on different result output formats the performance is so much better than using scripts.  
Thanks much!!

---

<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:** [August 20, 2018, 7:07pm UTC](https://discuss.elastic.co/t/aggregation-query-get-value-from-the-latest-doc/140795/5 "2018-08-20T19:07:50Z")

</div>

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