# Aggregate fields based on nested filter with custom separator

**URL:** <https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008>\
**Category:** Logstash\
**Created:** [August 23, 2017, 6:35am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008 "2017-08-23T06:35:20Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 23, 2017, 6:35am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/1 "2017-08-23T06:35:20Z")

</div>

I would like to aggregate some of the fields based on other nested fields.  
Here is how my data looks like:

```
{
          "place" => "abc",
   "m_id" => 5099,
  "address" => "Kurt-Schumacher-Ring 15-17",
         "name" => "delta ",
     "@version" => "1",
   "v_id" => 4540,
     "s_id" => 12560,
          "plz" => "12345"
}
{
          "place" => "cdf",
   "m_id" => 5099,
  "address" => "Kurt-Schumacher-Ring 15-17",
         "name" => "delta",
     "@version" => "1",
   "v_id" => 4540,
     "s_id" => 12560,
          "plz" => "12345"
}
{
          "place" => "cbc",
   "m_id" => 529,
  "address" => "Kurt-Schumacher-Ring 15-17",
         "name" => "delta",
     "@version" => "1",
   "v_id" => 165,
     "s_id" => 12568,
          "plz" => "69000"
 }
{
          "ort" => "vbf",
   "m_id" => 529,
  "address" => "Kurt-Schumacher-Ring 15-17",
         "name" => "delta ",
     "@version" => "1",
   "v_id" => 165,
     "s_id" => 12568,
          "plz" => "60000"
}

{
          "ort" => "xyz",
   "m_id" => 529,
  "address" => "Kurt-Schumacher-Ring 15-17",
         "name" => "delta ",
     "@version" => "1",
   "v_id" => 165,
     "s_id" => 12568,
          "plz" => "69999"
}

```

Now i want to aggregate them based on nested id [s\_id][v\_id][m\_id] or [s\_id][v\_id] or [s\_id][m\_id]

In the new output I want to create a new field "all" which should have  
place | plz address @ place | plz address

so for given input, following output

```
      "all" => "abc | 12345 Kurt-Schumacher-Ring 15-17 @ cdf | 12345 Kurt-Schumacher-Ring 15-17

```

"m\_id" =\> 5099,  
"name" =\> "delta ",  
"@version" =\> "1",  
"v\_id" =\> 4540,  
"s\_id" =\> 12560,  
"plz" =\> "12345"

```
   "all" => "cbc | 69000 Kurt-Schumacher-Ring 15-17 @ vbf | 60000 Kurt-Schumacher-Ring 15-17 @ xyz | 69999 Kurt-Schumacher-Ring"

```

"m\_id" =\> 529,  
"name" =\> "delta ",  
"@version" =\> "1",  
"v\_id" =\> 165,  
"s\_id" =\> 12568,

How can I do this, any pointers??

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 23, 2017, 6:36am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/2 "2017-08-23T06:36:08Z")

</div>

@fbaligand my new issue.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 23, 2017, 10:41am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/3 "2017-08-23T10:41:23Z")

</div>

My attempt to solve the problem so far:

```
filter {
aggregate {

  task_id => "%{[s_id][v_id]}"
  code => "
  map['all'] ||= 0; 
  map['all']=event['place']
  map['all'] = event ['plz']
  map['all']= event['address']
  "
 # map_action => "create"
  push_previous_map_as_event => true
  timeout => 5

  }
}

```

Doesnt work so far..

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 23, 2017, 10:48am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/4 "2017-08-23T10:48:56Z")

</div>

@magnusbaeck your input??

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [August 23, 2017, 12:52pm UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/5 "2017-08-23T12:52:22Z")

</div>

Which Logstash version do you use ?  
Looking to your input data, you have no nested field, right ?  
What is the input ? Jdbc ?

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 24, 2017, 4:43am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/6 "2017-08-24T04:43:24Z")

</div>

logstash version: logstash 5.5.1. Yeah this is jdbc input. I have nested fields, but, this aggregation is on parent data itself. So, for this particular case, no nested fields.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 24, 2017, 5:15am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/7 "2017-08-24T05:15:45Z")

</div>

I just want to combine the fields into new field using custom seperators here. Is aggregate filter plugin is not the right way to go for it? What else can i use here @fbaligand

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [August 24, 2017, 6:17am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/8 "2017-08-24T06:17:56Z")

</div>

- First, aggregate filter is the right plugin for your need, given you need to aggregate several events into one.
- concerning task\_id, syntax `"%{[s_id][v_id]}"` is for nested field "v\_id" inside "s\_id". You have not nested field. You want to make a composite task id based on 3 fields.
- `map['all']` should be initialized with an empty string, and not a `0` number, given you try to concatenate strings
- syntax `event['key']` doesn't work anymore since Logstash 5. It is replaced by `event.get('key')`
- if you try to concatenate fields, you should use `+=` operator and not `=` operator

So here's the right configuration :

```
aggregate {
    task_id => "%{s_id}%{v_id}%{m_id}"
  code => "
    map['all'] ||= ""
    map['all'] += " @ " unless map['all'].empty?
    map['all'] += event.sprintf('%{place} | %{plz} %{address}')
  "
  push_previous_map_as_event => true
  timeout => 5
}
```

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 24, 2017, 6:20am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/9 "2017-08-24T06:20:32Z")

</div>

i see! Now i understand it bit more. Thanks again!

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 24, 2017, 6:52am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/10 "2017-08-24T06:52:13Z")

</div>

dont know i m getting this error message, looked in hex editor too.

```
ERROR logstash.agent - Cannot create pipeline {:reason=>"Expected one of #, {, } at line 35, column 21 (byte 1649) after filter{\n aggregate {\n task_id => \"%{s_id}%{v_id}\"\n code => \"\n map['all'] ||= \""}
```

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 24, 2017, 7:07am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/11 "2017-08-24T07:07:03Z")

</div>

Ok, the problem is with the quotes. One has to use the single quotes instead of double. It works fine now.

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [August 24, 2017, 7:08am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/12 "2017-08-24T07:08:53Z")

</div>

Sorry for the double quotes.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 24, 2017, 10:51am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/13 "2017-08-24T10:51:22Z")

</div>

It is ok, gave me chance to learn more Ruby, logstash syntax. Between, I have another issue with this problem coming up now. Using the code above i get output, which is bit different than what i would expect.

I modified the code little bit, but, I am not sure how to proceed further.

```
filter{
aggregate {
 task_id => "%{s_id}%{v_id}"
code => "
map['all'] ||= ''
map['all'] += ' @ ' unless map['all'].empty?
map['all'] += event.sprintf('%{place} | %{plz} %{address}')
 event.set('all', map['all'])
"
push_previous_map_as_event => true
timeout => 5
}

}

```

I added event.set line which creates a new field called all. This is what i want in the end. But, it is printing all combined values in the end after the aggregation, i dont want to have that.

```
{
 “m_id” => 5099,
“name” => “delta “,
”@version” => “1”,
“v_id” => 4540,
“s_id” => 12560,
“plz” => “12345”
"all" => "abc | 12345 Kurt-Schumacher-Ring 15-17 @ cdf | 12345 Kurt-Schumacher-Ring 15-17"
}
{
 “m_id” => 529,
“name” => “delta “,
”@version” => “1”,
“v_id” => 165,
“s_id” => 12568,
 "all" => "cbc | 69000 Kurt-Schumacher-Ring 15-17 @ vbf | 60000 Kurt-Schumacher-Ring 15-17 @ xyz | 69999 Kurt-Schumacher-Ring"
}

```

It gives this additional values along with above one, i dont want this....  
{  
"all" =\> "abc | 12345 Kurt-Schumacher-Ring 15-17 @ cdf | 12345 Kurt-Schumacher-Ring 15-17"  
}  
{  
"all" =\> "cbc | 69000 Kurt-Schumacher-Ring 15-17 @ vbf | 60000 Kurt-Schumacher-Ring 15-17 @ xyz | 69999 Kurt-Schumacher-Ring"  
}

How to get rid of this ones? Is there any better way to proceed.

Another question, aggregate filter will take care of not repeating the duplicated entries??

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [August 25, 2017, 7:17am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/14 "2017-08-25T07:17:08Z")

</div>

It seems you don't understand aggregate filter behaviour when `push_previous_map_as_event => true` option is set.  
I really invite you to read aggregate documentation which is really detailled, especially example 4 :  
[https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html)

The main idea is :

- for a same entity id, jdbc input generates one event per join
- aggregate filter will aggregate jdbc events for a same entity id into one new aggregated event created from map.

So it is normal, at the end, for each task id, to have one new event with only "all" field. Because you only set "all" field inside.

If I understand your need (I'm not sure), at the end, you want to have only one event per task\_id, with aggregated all field.

If so, here's the right configuration up to me :

```
aggregate {
 task_id => "%{s_id}%{v_id}"
code => "
map.merge(event.to_hash) if map.empty?
map['all'] ||= ''
map['all'] += ' @ ' unless map['all'].empty?
map['all'] += event.sprintf('%{place} | %{plz} %{address}')
event.cancel()
"
push_previous_map_as_event => true
timeout => 5
}

```

- aggregate filter will not remove intermediate events
- furthermore, aggregate filter will do only what your `code` indicates.
- so it won't automatically remove duplicates.
- but the way to do that is to copy all you need from event to map, then cancel event (so it is deleted), and at the end, aggregate generates a new event from map with all aggregated data, one event per task\_id. So task\_id definition is the key to avoid duplicates.

---

<div class="post-metadata">

**Author:** ![malhotras](https://avatars.discourse-cdn.com/v4/letter/m/a8b319/32.png) [@malhotras](https://discuss.elastic.co/u/malhotras)\
**Post date:** [August 25, 2017, 7:48am UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/15 "2017-08-25T07:48:36Z")

</div>

Yeah, i understand it. I understand that the duplicates are taken care of, I just wanted to confirm it. As, i did not make a test on my data to make sure it is working well.

Tested the script now. The confusion happend as I was printing the output as stdout (rubydebug). I think it is clear to me now.

I have some additional queries, more of ruby. If you could help here.

I want to modify the output such as digit to %02d and Year to last 2 digits only. So, here is how my string looks like now.

map['date'] += event.sprintf('%{group1}-%{group2}- **%02d%{under}-%{year}**')

I have to edit highlighted text here. under is single digit number want to print it as 02D and year is 4 digit want to print its last 2 digit only. Similarly i want to do uppercase and lowecase for some fields.

---

<div class="post-metadata">

**Author:** ![fbaligand](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/fbaligand/32/5657_2.png) [@fbaligand](https://discuss.elastic.co/u/fbaligand)\
**Post date:** [August 25, 2017, 8:39pm UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/16 "2017-08-25T20:39:16Z")

</div>

All you can do with "sprintf" is explained here :  
[https://www.elastic.co/guide/en/logstash/5.5/event-dependent-configuration.html#sprintf](https://www.elastic.co/guide/en/logstash/5.5/event-dependent-configuration.html#sprintf)

There is especially a link to detail all "time format" possibilities.

---

<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:** [September 22, 2017, 8:39pm UTC](https://discuss.elastic.co/t/aggregate-fields-based-on-nested-filter-with-custom-separator/98008/17 "2017-09-22T20:39:36Z")

</div>

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