# Finding optimal solution to use filter in aggregations of transform index **URL:** \ **Category:** Kibana\ **Tags:** transforms\ **Created:** [July 28, 2020, 10:32pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980 "2020-07-28T22:32:48Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![InfiniteDreamer](https://avatars.discourse-cdn.com/v4/letter/i/779978/32.png) [@InfiniteDreamer](https://discuss.elastic.co/u/InfiniteDreamer)\ **Post date:** [July 28, 2020, 10:32pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/1 "2020-07-28T22:32:48Z")
We are trying to create transform index, Following sample data is our subset where we want to find most recent event date based our duty code. ```auto code enterprise duty visitdate 2247 HASTY&TASTY 1 Jul 27, 2020 @ 00:00 2247 HASTY&TASTY 2 Jul 26, 2020 @ 00:00 2247 HASTY&TASTY 0 Jul 25, 2020 @ 00:00 2247 HASTY&TASTY 2 Jun 30, 2020 @ 00:00 2247 HASTY&TASTY 1 Jun 22, 2020 @ 00:00 2213 DunkinDonut 0 Jul 28, 2020 @ 00:00 2213 DunkinDonut 2 Jul 27, 2020 @ 00:00 2213 DunkinDonut 2 Jul 26, 2020 @ 00:00 ``` Transform index should have only most recent dated docs where duty=2. ```auto code enterprise visitdate 2247 HASTY&TASTY Jul 26, 2020 @ 00:00 2213 DunkinDonut Jul 27, 2020 @ 00:00 ``` This script for transformation is working fine, where we want to know any other alternate method to implement the same, because it required multiple scripted fields for multiple use cases in same transform index where it may create loading issues. ```auto POST _transform/_preview { "source": { "index": [ "visitdata*" ], "query": { "exists": { "field": "visitdate" } } }, "dest": { "index": "nx009" }, "pivot": { "group_by": { "code": { "terms": { "field": "code.keyword" } }, "enterprise": { "terms": { "field": "enterprise.keyword" } } }, "aggregations": { "visitdate_doc": { "scripted_metric": { "init_script": "state.timestamp_latest = 0L; state.last_doc = ''", "map_script": """ def current_date = doc['visitdate'].getValue().toInstant().toEpochMilli(); def visited = doc['duty'].getValue(); if (current_date > state.timestamp_latest && visited==2 ) {state.timestamp_latest = current_date; state.last_doc = new HashMap(params['_source']);} """, "combine_script": "return state", "reduce_script": """ def last_doc = ''; def timestamp_latest = 0L; for (s in states) { if (s.timestamp_latest > (timestamp_latest)) { timestamp_latest = s.timestamp_latest; last_doc = s.last_doc; } }if(last_doc != null && !last_doc.isEmpty()) { return last_doc.visitdate; } """ } } } } } ``` ---
**Author:** ![InfiniteDreamer](https://avatars.discourse-cdn.com/v4/letter/i/779978/32.png) [@InfiniteDreamer](https://discuss.elastic.co/u/InfiniteDreamer)\ **Post date:** [July 30, 2020, 12:10pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/2 "2020-07-30T12:10:43Z")
Hi, kindly guide us ---
**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\ **Post date:** [July 30, 2020, 1:32pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/3 "2020-07-30T13:32:47Z")
Hi, I do not see anything wrong with your approach. Getting the last state is one of the top ask for transform and we might have a ootb solution for that in future. Today you need `scripted_metric`, we know this is complicated and fragile, but as said, there is no alternative at the moment. Regarding your config: Are you always only interested in data points with `duty == 2` (or `duty>=2`)? If so, I think it is better for performance, if you filter in the query, instead of filtering as part of `scripted_metric`. It would also be possible to put a `filter` aggregation right before `scripted_metric`, in case you do not want to filter globally. ---
**Author:** ![InfiniteDreamer](https://avatars.discourse-cdn.com/v4/letter/i/779978/32.png) [@InfiniteDreamer](https://discuss.elastic.co/u/InfiniteDreamer)\ **Post date:** [July 30, 2020, 7:40pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/4 "2020-07-30T19:40:19Z")
Thanks @Hendrik_Muhs Actually we want to have many max dates based on individual duty codes using multiple `scripted_metric` fields in same transform index, index filtering or filter aggregation may not suit for our case. ---
**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\ **Post date:** [July 31, 2020, 9:11am UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/5 "2020-07-31T09:11:15Z")
FWIW a sub-agg filter would look like this: ```auto { "source": { ... }, "pivot": { "group_by": { ... }, "aggregations": { "visited_twice": { "filter": { "term": { "visited": 2 } }, "aggs": { "visitdate_doc": { "scripted_metric": { ... ``` That means you filter out `visited!=2` before the `scripted_metric` (and you can remove the check in the script). Note that the result would have an additional level (can be later moved using an ingest pipeline): ```auto "visited_twice": { "visitdate_doc": { ... } } ``` This solution should work, however I can not say if/how much performance you gain. (FWIW our benchmark tool rally has support for transform.) ---
**Author:** ![InfiniteDreamer](https://avatars.discourse-cdn.com/v4/letter/i/779978/32.png) [@InfiniteDreamer](https://discuss.elastic.co/u/InfiniteDreamer)\ **Post date:** [August 11, 2020, 1:29pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/6 "2020-08-11T13:29:56Z")
Hi @Hendrik_Muhs , we are creating few fields based on above sub-agg filter with range, where we want to create a fields based on 30 day and 45 day... avg value in transform index. ```auto { "source": { "index": [ "sales*" ] }, "pivot": { "group_by": { "customer.keyword": { "terms": { "field": "customer.keyword" } }, "locationcode.keyword": { "terms": { "field": "locationcode.keyword" } } }, "aggregations": { "avg30days": { "filter": { "range": { "journeydate": { "gte": "now-30d/d", "lte": "now/d" } } }, "aggs": { "avg_30val": { "avg": { "field": "linenetamount" } } } }, "avg45days": { "filter": { "range": { "journeydate": { "gte": "now-45d/d", "lte": "now/d" } } }, "aggs": { "avg_45val": { "avg": { "field": "linenetamount" } } } } } } } ``` this is the error we are getting. ```auto { "error": { "root_cause": [ { "type": "status_exception", "reason": "Unsupported aggregation type [filter]" } ], "type": "status_exception", "reason": "Failed to validate configuration", "caused_by": { "type": "status_exception", "reason": "Unsupported aggregation type [filter]" } }, "status": 400 } ``` please let us know how to rectify this error or any other logic to acheive the same. ---
**Author:** ![BenTrent](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bentrent/32/33915_2.png) [@BenTrent](https://discuss.elastic.co/u/BenTrent)\ **Post date:** [August 11, 2020, 2:16pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/7 "2020-08-11T14:16:27Z")
Hey @InfiniteDreamer, `filter` agg support was added in Elasticsearch 7.7. Can you confirm your version? ---
**Author:** ![InfiniteDreamer](https://avatars.discourse-cdn.com/v4/letter/i/779978/32.png) [@InfiniteDreamer](https://discuss.elastic.co/u/InfiniteDreamer)\ **Post date:** [August 12, 2020, 5:29am UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/8 "2020-08-12T05:29:10Z")
Hi @BenTrent, Thanks for the reply, we are checking earlier transform script on Elasticsearch 7.5. Also now i cross verified the feature with Elasticsearch 7.8 on sample ecommerce data, though transform runs but it throws null value in place of avg aggs, ```auto POST _transform/_preview { "source": { "index": [ "kibana_sample_data_ecommerce*" ] }, "pivot": { "group_by": { "customer_full_name.keyword": { "terms": { "field": "customer_full_name.keyword" } }, "day_of_week": { "terms": { "field": "day_of_week" } } }, "aggregations": { "avg7days": { "filter": { "range": { "order_date": { "gte": "now-7d/d", "lte": "now/d" } } }, "aggs": { "avg_7val": { "avg": { "field": "taxful_total_price" } } } }, "avg3days": { "filter": { "range": { "order_date": { "gte": "now-3d/d", "lte": "now/d" } } }, "aggs": { "avg_3val": { "avg": { "field": "taxful_total_price" } } } } } } } ``` but taxful total price is present in the docs. ```auto { "preview" : [ { "customer_full_name" : { "keyword" : "Abd Adams" }, "avg7days" : { "avg_7val" : null }, "avg3days" : { "avg_3val" : null }, "day_of_week" : "Friday" }, { "customer_full_name" : { "keyword" : "Abd Allison" }, "avg7days" : { "avg_7val" : null }, "avg3days" : { "avg_3val" : null }, "day_of_week" : "Sunday" }............ ``` Kindly help us ---
**Author:** ![BenTrent](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bentrent/32/33915_2.png) [@BenTrent](https://discuss.elastic.co/u/BenTrent)\ **Post date:** [August 12, 2020, 2:58pm UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/9 "2020-08-12T14:58:27Z")
You are grouping by `day_of_week`. So, your buckets will be the customer and each individual day of week (like `SUNDAY`, `MONDAY`, etc.). Since you are filtering on `now-7d`, that means that if that particular customer say `John` did not place an order on a `Monday` in the last 7 days, that value will be null as it does not exist. FWIW, I tried this myself, and did get some results, but yes, there are many null ones. Which makes sense, as not every customer makes an order every day of the week. ---
**Author:** ![InfiniteDreamer](https://avatars.discourse-cdn.com/v4/letter/i/779978/32.png) [@InfiniteDreamer](https://discuss.elastic.co/u/InfiniteDreamer)\ **Post date:** [August 14, 2020, 7:59am UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/10 "2020-08-14T07:59:09Z")
Thanks @BenTrent, Instead of Avg other metrics like sum, distinct can group the sample data ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 11, 2020, 7:59am UTC](https://discuss.elastic.co/t/finding-optimal-solution-to-use-filter-in-aggregations-of-transform-index/242980/11 "2020-09-11T07:59:15Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.