# Subselect filter for aggragation

**URL:** <https://discuss.elastic.co/t/subselect-filter-for-aggragation/306054>\
**Category:** Elasticsearch\
**Created:** [May 31, 2022, 4:20pm UTC](https://discuss.elastic.co/t/subselect-filter-for-aggragation/306054 "2022-05-31T16:20:51Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![frankmontyne](https://avatars.discourse-cdn.com/v4/letter/f/c6cbf5/32.png) [@frankmontyne](https://discuss.elastic.co/u/frankmontyne)\
**Post date:** [May 31, 2022, 4:20pm UTC](https://discuss.elastic.co/t/subselect-filter-for-aggragation/306054/1 "2022-05-31T16:20:51Z")

</div>

Hi,

I have been searching for a way to solve the following problem by performing a query or aggregation instead of looping through the data and filtering the results myself. I haven't found a solution up until now and was wondering if someone could tell me if it is at all possible in Elasticsearch. The use case is as follows:

Filter on 'last' events with latest entry date.

Sometimes events are ingested multiple times for the same timeseries. This happens when there are newer 'versions' of the same data.

We don't want to replace the existing event, but we ingest it with a different entry date, which provides us with the possibility later on also to do comparisons and analysis.

Example:

- price for 1 april 2022 is 50 on date 1/1/2022, and 60 on date 5/1/2022.

- price for 2 april 2022 is 10 on date 1/1/2022, and 5 on date 10/2/2022.

We will have 4 events then:

- event 1
  - value: 50
  - timestamp: 1/4/2022
  - entry date: 1/1/2022

- event 2
  - value: 60
  - timestamp: 1/4/2022
  - entry date: 5/1/2022

- event 3
  - value: 10
  - timestamp: 2/4/2022
  - entry date: 1/1/2022

- event 4
  - value: 5
  - timestamp: 2/4/2022
  - entry date: 10/2/2022

But when retrieving or aggregating the events we want to filter:

- only the 'last' event with the most recent entry date
  - Would result in:
    - timestamp: 1/4/2022 → value: **60** (entry date: 5/1/2022)
    - timestamp: 2/4/2022 → value: **5** (entry date: 10/2/2022)

- 'last' before a given reference date (to exclude future events)
  - with reference date **1/2/2022** , would result in:
    - timestamp: 1/4/2022 → value: **60** (entry date: 5/1/2022)
    - timestamp: 2/4/2022 → value: **10** (entry date: 1/1/2022)

  - with reference date **1/3/2022** , would result in:
    - timestamp: 1/4/2022 → value: **60** (entry date: 5/1/2022)
    - timestamp: 2/4/2022 → value: **5** (entry date: 1/1/2022)

So basically I could do a DateHistogram aggregation on timestamp (fixed interval 1 ms) which would group all the events per timestamp. But then somehow I should be able to filter on the entry date within each group to select the event I want and return the value.

Any help is greatly appreciated!

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [June 1, 2022, 10:09am UTC](https://discuss.elastic.co/t/subselect-filter-for-aggragation/306054/2 "2022-06-01T10:09:13Z")

</div>

Of course you can do it by [sub-aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations.html#run-sub-aggs).

Something like:

```auto
{
  "size":0,
  "query":{
    "range":{
      "timestamp":{
        "lte": "2022-02-01"
      }
    }
  },
  "aggs":{
    "d":{
      "terms":{
        "field": "entry_date"
      },
      "aggs":{
        "latest":{
          "top_metrics": {
            "metrics": {"field": "price"},
            "sort": [
              {
                "timestamp": {
                  "order": "desc"
                }
              }]
          }
        }
      }
    }
  }
}

```

Or you may use top\_hits aggregation if you need other information from the document.

---

<div class="post-metadata">

**Author:** ![frankmontyne](https://avatars.discourse-cdn.com/v4/letter/f/c6cbf5/32.png) [@frankmontyne](https://discuss.elastic.co/u/frankmontyne)\
**Post date:** [June 1, 2022, 1:24pm UTC](https://discuss.elastic.co/t/subselect-filter-for-aggragation/306054/3 "2022-06-01T13:24:42Z")

</div>

> [@Tomo\_M](#):
>
> ```auto
> "aggs":{
> "d":{
> "terms":{
> "field": "entry_date"
> },
> "aggs":{
> "latest":{
> "top_metrics": {
> "metrics": {"field": "price"},
> "sort": [
> {
> "timestamp": {
> "order": "desc"
> }
> }]
> }
> }
> }
> }
> }
> 
> ```

Thanks, that did the trick, just needed to set the size on the terms aggregation to get all buckets!

---

<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:** [June 29, 2022, 1:24pm UTC](https://discuss.elastic.co/t/subselect-filter-for-aggragation/306054/4 "2022-06-29T13:24:46Z")

</div>

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