# Filter based on scripted field in an aggregation **URL:** \ **Category:** Elasticsearch\ **Created:** [April 21, 2018, 5:30pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976 "2018-04-21T17:30:18Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![nishantkumar](https://avatars.discourse-cdn.com/v4/letter/n/db5fbb/32.png) [@nishantkumar](https://discuss.elastic.co/u/nishantkumar)\ **Post date:** [April 21, 2018, 5:30pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/1 "2018-04-21T17:30:18Z")
Here is my source: ``` { "size": 0, "query": { "bool": { "must": [ { "range": { "datetime": { "gte": "2017-04-01T00:00:00.000Z", "lte": "2018-03-31T23:59:59.999Z" } } } ] } }, "aggs": { "all_match": { "filters": { "filters": { "all": { "match_all": {} } } }, "aggs": { "jobs": { "terms": { "field": "job_num", "size": 200000 }, "aggs": { "latest_job": { "top_hits": { "size": 1, "sort": [{"rec_date": "desc"}], "script_fields": { "reqd_flag":{ "script": { "lang": "painless", "inline": "params['_source']['required_flag'] == 'Y' ? 0 : 1" } } } } } } } } } } } ``` I need to filter out the records returned in ' **latest\_job**' aggregation based on the value present in the field ' **reqd\_flag**'. **NOTE** : ' **all\_match**' aggregation was created as a work around to apply bucket\_path. Any input / pointers / suggestions are appreciated. Thank you in advance. [StackOverflow link](https://stackoverflow.com/posts/49962778/edit) [GitHub link](https://github.com/elastic/elasticsearch/issues/29648) ---
**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\ **Post date:** [April 27, 2018, 4:19pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/2 "2018-04-27T16:19:57Z")
So you only want documents that have `required_flag: 1` to be present in `latest_job`? If that's the case, you just need to add a filter in the query, or in the `all_match` filter aggregation checking the `required_flag` field. No need to put it inside a script in `top_hits` ---
**Author:** ![nishantkumar](https://avatars.discourse-cdn.com/v4/letter/n/db5fbb/32.png) [@nishantkumar](https://discuss.elastic.co/u/nishantkumar)\ **Post date:** [May 14, 2018, 2:00pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/3 "2018-05-14T14:00:24Z")
Thank you for responding back @polyfractal, and my apologies for not reverting back earlier than now. It is not the field 'required\_flag' but the calculated field 'reqd\_flag' that I need to filter on. It is just a sample condition that I have placed for evaluating 'reqd\_flag' here in example but it will be a compound condition and based on the evaluated value, I want to filter the records. ---
**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\ **Post date:** [May 14, 2018, 2:06pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/4 "2018-05-14T14:06:46Z")
I think you'll want to use the [Script Query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-query.html) in that case. You still might be able to put that inside the main query, which is preferable. It will be much faster if you evaluate the script as part of the query condition, assuming you're ok with the query evaluating across all documents (and not a subset. Alternatively, you could put the script query inside of a [Filter Aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html), which allows you to apply the aggregation to just a subset of the data. But do note that the filter aggregation will be much slower, since it can't take advantage of the optimizations that are applied at query time. TopHits are only designed to show the "top" document in a bucket. You can't hook any logic or filtering to the the top hit, it's purely for display purposes. ---
**Author:** ![nishantkumar](https://avatars.discourse-cdn.com/v4/letter/n/db5fbb/32.png) [@nishantkumar](https://discuss.elastic.co/u/nishantkumar)\ **Post date:** [May 14, 2018, 2:29pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/5 "2018-05-14T14:29:17Z")
That gives me some perspective, but I would like to state my problem here in much simpler words: STEP 1: Based on some conditions, fetch the list of records (all these filtered records have a field, say 'rec\_id' which will be unique for them all. There will be multiple records for each 'rec\_id' one for each day present in the field 'datetime') STEP 2: Use the above obtained list of 'rec\_ids' to obtain the latest record based on 'datetime' field' STEP 3: Apply the filters applied in STEP 1 to filter out if any incorrect data in the latest set of records. Any input / suggestions / queries are welcome here. ---
**Author:** ![polyfractal](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/polyfractal/32/48162_2.png) [@polyfractal](https://discuss.elastic.co/u/polyfractal)\ **Post date:** [May 14, 2018, 2:33pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/6 "2018-05-14T14:33:11Z")
Gotcha... should be doable. Would it be possible to share a few sample documents and the criteria used to filter? I think it'd be easiest if I were to just write up a quick demonstration query, rather than trying to explain the steps. ---
**Author:** ![nishantkumar](https://avatars.discourse-cdn.com/v4/letter/n/db5fbb/32.png) [@nishantkumar](https://discuss.elastic.co/u/nishantkumar)\ **Post date:** [May 14, 2018, 2:45pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/7 "2018-05-14T14:45:06Z")
The above shared transform was a sample of it. There are a set of conditions that I need to add in global filter and the same needs to be applied once I get the latest record. Does that help ? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 11, 2018, 2:45pm UTC](https://discuss.elastic.co/t/filter-based-on-scripted-field-in-an-aggregation/128976/8 "2018-06-11T14:45:10Z")
Closed on June 11, 2018, 2:45pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.