# Reduce output from Aggregation based on date range

**URL:** <https://discuss.elastic.co/t/reduce-output-from-aggregation-based-on-date-range/137078>\
**Category:** Elasticsearch\
**Created:** [June 22, 2018, 7:26pm UTC](https://discuss.elastic.co/t/reduce-output-from-aggregation-based-on-date-range/137078 "2018-06-22T19:26:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![MRussell](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@MRussell](https://discuss.elastic.co/u/MRussell)\
**Post date:** [June 22, 2018, 7:26pm UTC](https://discuss.elastic.co/t/reduce-output-from-aggregation-based-on-date-range/137078/1 "2018-06-22T19:26:59Z")

</div>

Hi

I am new to Elasticsearch and trying to find my way through the development language.

Is it possible to reduce the number of records returned from an aggregation based on the aggregated field?

I have seen a couple of examples where this has been done using the "script" functionality on an integer, but I would like to be able to do it on a dynamic date (now-???).

This is what I have so far and the field I want to reduce on is "max\_login".

Does anyone have any suggestions?

Thanks  
Mark

{  
"size": 0,  
"query": {  
"bool": {  
"must": [  
{  
"query\_string": {  
"query": "action: authenticated"  
}  
},  
{  
"range": {"@timestamp": {"gte": "now-90d/d"}}}  
]  
}  
},  
"aggs": {  
"by\_username": {  
"terms": {"field": "username.keyword",  
"size": 10},  
"aggs": {  
"max\_login": {"max": {"field": "@timestamp"}}  
}  
}  
}  
}

---

<div class="post-metadata">

**Author:** ![MRussell](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@MRussell](https://discuss.elastic.co/u/MRussell)\
**Post date:** [June 25, 2018, 1:55pm UTC](https://discuss.elastic.co/t/reduce-output-from-aggregation-based-on-date-range/137078/2 "2018-06-25T13:55:51Z")

</div>

Ok, I have it working using the bucket filter. My next challenge is, how do I format the date in the filter to be a "YYYY-MM-DD" format?

```
GET /redshift-dev*/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "action: authenticated"
          }
        },
        {
          "range": {"@timestamp": {"gte": "now-90d/d"}}}
      ]
    }
  },

  "aggs": {
    "by_username": {
      "terms": {"field": "username.keyword",
      "size": 10},
          "aggs": {
            "max_login": {"max": {"field": "@timestamp","format": "YYYY-MM-dd"}},
          
          "login_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "maxLogin": "max_login"
                        },
                        "script": "params.maxLogin < **1529525488770L**"
                    }
                }
          }
    }
  }
}
```

---

<div class="post-metadata">

**Author:** ![MRussell](https://avatars.discourse-cdn.com/v4/letter/m/bc8723/32.png) [@MRussell](https://discuss.elastic.co/u/MRussell)\
**Post date:** [June 25, 2018, 8:05pm UTC](https://discuss.elastic.co/t/reduce-output-from-aggregation-based-on-date-range/137078/3 "2018-06-25T20:05:29Z")

</div>

Solved it, finally.

The following code works out if a user has not logged in for the last 90 days, with the scope of logins being since the last scan (6 months ago).

```
{
  "size": 0,
  "query": {
    "bool": {
      "must": [
        {
          "query_string": {
            "query": "action: authenticated"
          }
        },
        {
          "range": {"@timestamp": {"gte": "now-180d/d"}}}
      ]
    }
  },
  
  "aggs": {
    "by_username": {
      "terms": {"field": "username.keyword", "size": 2147483647},
          "aggs": {
            "max_login": {"max": {"field": "@timestamp", "format" : "YYYY-MM-dd"}},
          "login_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                          "maxLogin": "max_login"
                        },
                        "script": "params.maxLogin <= new Date().getTime()- 7776000000L"
                    }
                }
          }
    }
  }
}
```

---

<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:** [July 23, 2018, 8:05pm UTC](https://discuss.elastic.co/t/reduce-output-from-aggregation-based-on-date-range/137078/4 "2018-07-23T20:05:30Z")

</div>

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