# How to exclude duplicates in Aggregate filter? **URL:** \ **Category:** Logstash\ **Created:** [July 20, 2018, 3:36pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939 "2018-07-20T15:36:57Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 20, 2018, 3:36pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/1 "2018-07-20T15:36:58Z")
Hello! I'm fetching some data from db with multiple associated tables. My query: ``` SELECT `item`.`id`, `item`.`name`, `colour`.`name` AS `colour`, `material`.`name` AS `material` FROM `item` LEFT JOIN `item_has_colour` AS `ic` ON `ic`.`item` = `item`.`id` LEFT JOIN `colour` ON `colour`.`id` = `ic`.`colour` LEFT JOIN `item_has_material` AS `im` ON `im`.`item` = `item`.`id` LEFT JOIN `material` ON `material`.`id` = `im`.`material` ORDER BY `item`.`id` ``` It produces the next dataset: ``` mysql> SELECT `item`.`id`, `item`.`name`, `colour`.`name` AS `colour`, `material`.`name` AS `material` FROM `item` LEFT JOIN `item_has_colour` AS `ic` ON `ic`.`item` = `item`.`id` LEFT JOIN `colour` ON `colour`.`id` = `ic`.`colour` LEFT JOIN `item_has_material` AS `im` ON `im`.`item` = `item`.`id` LEFT JOIN `material` ON `material`.`id` = `im`.`material` ORDER BY `item`.`id`; +----+-------+--------+----------+ | id | name | colour | material | +----+-------+--------+----------+ | 1 | cat | red | ceramic | | 1 | cat | green | ceramic | | 1 | cat | yellow | ceramic | | 1 | cat | red | plastic | | 1 | cat | green | plastic | | 1 | cat | yellow | plastic | | 2 | dog | red | ceramic | | 2 | dog | yellow | ceramic | | 3 | mouse | red | ceramic | | 3 | mouse | blue | ceramic | | 3 | mouse | red | plastic | | 3 | mouse | blue | plastic | +----+-------+--------+----------+ 12 rows in set (0,00 sec) ``` So here are all possible combinations present. Of all this redundant data I need only `colour`s an `material`s sets for each `item`. But obviously, when I aggregate all this with filter: ``` filter { aggregate { task_id => "%{id}" code => " map['id'] = event.get('id') map['name'] = event.get('name') map['colours'] ||= [] map['colours'] << {'colour' => event.get('colour')} map['materials'] ||= [] map['materials'] << {'material' => event.get('material')} event.cancel() " push_previous_map_as_event => true timeout => 3 } } ``` I'm getting a whole lot of duplicates: ``` { "name" => "cat", "@timestamp" => 2018-07-20T15:27:37.393Z, "@version" => "1", "colours" => [ [0] { "colour" => "red" }, [1] { "colour" => "green" }, [2] { "colour" => "yellow" }, [3] { "colour" => "red" }, [4] { "colour" => "green" }, [5] { "colour" => "yellow" } ], "id" => 1, "materials" => [ [0] { "material" => "ceramic" }, [1] { "material" => "ceramic" }, [2] { "material" => "ceramic" }, [3] { "material" => "plastic" }, [4] { "material" => "plastic" }, [5] { "material" => "plastic" } ] } ``` Had tried to get rid of duplicates with conditional `not in` statement, but simply can't match the syntax. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 20, 2018, 3:55pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/2 "2018-07-20T15:55:50Z")
``` h = { "colour" => event.get("colour") } if ! a.include?(h) a << h end ``` ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [July 20, 2018, 4:14pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/3 "2018-07-20T16:14:35Z")
It works! The only remark the double quotes in your example should be replaced with single ones. Thank you for help! By the way, what language is this? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 20, 2018, 5:02pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/4 "2018-07-20T17:02:33Z")
It is Ruby. For code options you can use either ``` code => ' map["id"] = event.get("id") ' ``` or ``` code => " map['id'] = event.get('id') " ``` I lean towards the former, the documentation tends to use the latter. ---
**Author:** ![gowt](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@gowt](https://discuss.elastic.co/u/gowt)\ **Post date:** [July 31, 2018, 9:46pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/5 "2018-07-31T21:46:44Z")
Hi i am using following filter code where i am getting duplicate 'Specality' code =\> " map['SpecilaityList'] ||= [] map['SpecilaityList'] \<\< { 'Specialty' =\> event.get('Specialty') } event.cancel() " Can you please assist the ruby part to exclude duplicate values. I am getting syntax issues for my tries. ---
**Author:** ![Jevgenij](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jevgenij/32/33543_2.png) [@Jevgenij](https://discuss.elastic.co/u/Jevgenij)\ **Post date:** [August 1, 2018, 10:30am UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/6 "2018-08-01T10:30:05Z")
``` map['SpecilaityList'] ||= [] sl = {'Specialty' => event.get('Specialty')} if ! map['SpecilaityList'].include?(sl) map['SpecilaityList'] << sl end ``` Also you might want to use `specialty` instead of `Specialty`, because of lowercase everywhere here by default, AFAIK. ---
**Author:** ![gowt](https://avatars.discourse-cdn.com/v4/letter/g/a4c791/32.png) [@gowt](https://discuss.elastic.co/u/gowt)\ **Post date:** [August 1, 2018, 4:53pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/7 "2018-08-01T16:53:00Z")
It worked. thank you very much. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 29, 2018, 4:53pm UTC](https://discuss.elastic.co/t/how-to-exclude-duplicates-in-aggregate-filter/140939/8 "2018-08-29T16:53:00Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.