# Aggregate filter plugin - nested aggregation

**URL:** <https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192>\
**Category:** Logstash\
**Created:** [July 30, 2018, 2:02pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192 "2018-07-30T14:02:05Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![NickRbk](https://avatars.discourse-cdn.com/v4/letter/n/82dd89/32.png) [@NickRbk](https://discuss.elastic.co/u/NickRbk)\
**Post date:** [July 30, 2018, 2:02pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/1 "2018-07-30T14:02:05Z")

</div>

Hello!

I am trying to aggregate some data from DB by Logstash.  
My data in db looks like this:

+----+----------------+---------------------+----------------------+----------------------+  
| # | product\_id | product\_name | property\_name | property\_value |  
+----+----------------+---------------------+----------------------+----------------------+  
| 1 | 100 | pc | colour | black |  
+----+----------------+---------------------+----------------------+----------------------+  
| 2 | 100 | pc | colour | silver |  
+----+----------------+---------------------+----------------------+----------------------+  
| 3 | 100 | pc | ram | 16Gb |  
+----+----------------+---------------------+----------------------+----------------------+  
| 4 | 100 | pc | hdd | 200Gb |  
+----+----------------+---------------------+----------------------+----------------------+  
| 5 | 101 | printer | colour | black |  
+----+----------------+---------------------+----------------------+----------------------+  
| 6 | 101 | printer | features | wifi |  
+----+----------------+---------------------+----------------------+----------------------+  
| 7 | 101 | printer | features | scanner |  
+----+----------------+---------------------+----------------------+----------------------+  
| 8 | 101 | printer | type | mate |  
+----+----------------+---------------------+----------------------+----------------------+  
| 9 | 102 | laptop | features | wifi 5Ghz |  
+----+----------------+---------------------+----------------------+----------------------+  
| 10 | 102 | laptop | colour | white |  
+----+----------------+---------------------+----------------------+----------------------+  
| 11 | 102 | laptop | hdd | 512Gb |  
+----+----------------+---------------------+----------------------+----------------------+

I want to aggregate data by product\_id, property\_name in the following way:

```
[
    {
        "id": 100,
        "name": "pc",
        "properties": {
            "colour": [
                "black",
                "silver"
            ],
            "hdd": [
                "200Gb"
            ],
            "ram": [
                "16Gb"
            ]
        }
    },
    {
        "id": 101,
        "name": "printer",
        "properties": {
            "features": [
                "wifi",
                "scanner"
            ],
            "colour": [
                "black"
            ],
            "type": [
                "mate"
            ]
        }
    },
    {
        "id": 102,
        "name": "laptop",
        "properties": {
            "features": [
                "wifi 5Ghz"
            ],
            "colour": [
                "white"
            ],
            "hdd": [
                "512Gb"
            ]
        }
    }
]

```

For this purpose I am trying to use aggregate filter plugin and read [example #4 of docs](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html) and [this topic](https://discuss.elastic.co/t/aggregate-filter-plugin-need-help-to-make-it-work/140901).

Here is my `logstash.conf` (_filter part_):

```
filter {
  aggregate {
    task_id => "%{product_id}"
    code => "
      map['product_id'] = event.get('product_id')
      map['product_name'] = event.get('product_name')
      map['properties'] ||= {}

      map[event.get('property_name')] ||= []                                         
      map[event.get('property_name')] << event.get('property_value') 

      event.cancel()
    "
    push_previous_map_as_event => true
    timeout => 3
  }
}

```

The output of filtering is:

```
{"product_id":100, "product_name":"pc", "properties":[], "colour":["black","silver"], "ram":["16Gb"], "hdd":["200Gb"]}
{"product_id":101, "product_name":"printer", "properties":[], "colour":["black"], "type":["mate"], "features":["wifi","scanner"]}
{"product_id":102, "product_name":"laptop", "properties":[], "colour":["white"],"hdd":["512Gb"], "features":["wifi 5Ghz"]}

```

But I need that properties (like "colour", "ram", "hdd") will be inside "properties" field.  
For this purpose I tried to use

```
map['properties'] ||= []
map['properties'] << {
   map[event.get('property_name')] ||= []                                         
   map[event.get('property_name')] << event.get('property_value') 
}

```

But that doesn't work.  
I'm not familiar with that syntax, so any idea how to put properties (like "colour", "ram", "hdd") inside "properties" ? Am I missing something?  
Thank you!

---

<div class="post-metadata">

**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 30, 2018, 2:34pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/2 "2018-07-30T14:34:15Z")

</div>

```
event.set("[properties][" + event.get('property_name') + "]", ...)
```

---

<div class="post-metadata">

**Author:** ![NickRbk](https://avatars.discourse-cdn.com/v4/letter/n/82dd89/32.png) [@NickRbk](https://discuss.elastic.co/u/NickRbk)\
**Post date:** [July 30, 2018, 3:44pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/3 "2018-07-30T15:44:24Z")

</div>

Could you explain, instead of what or how I should use this code ?  
I tried to use it as

```
map['properties'] <<
        event.set('[properties][' + event.get('property_name') + ']', event.get('property_value'))

```

The result of above line in ''properties" field like

```
"properties":["black","silver","16Gb","200Gb"]
"properties":["black","wifi","scanner","mate"]
...

```

if I use at a new line `event.set('[properties][' + event.get('property_name') + ']', event.get('property_value'))` my "properties" field is empty.

But my expectation is:

```
"properties": {
  "colour": ["black","silver"],
  "hdd": ["200Gb"],
  "ram": ["16Gb"]
}
```

---

<div class="post-metadata">

**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 30, 2018, 4:30pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/4 "2018-07-30T16:30:39Z")

</div>

Sorry, I made the incorrect assumption that if the syntax worked with event.set it would also work with an insertion into map. I was wrong.

So I would fix this by adding a mutate filter after the aggregate.

```
mutate {
    rename => { 
         "colour" => "[properties][colour]" 
         "features" => "[properties][features]"
         "hdd" => "[properties][hdd]"
     }
}

```

It is counter-intuitive to me, but although aggregate drops every event, it also creates events that continue down the pipeline.

---

<div class="post-metadata">

**Author:** ![NickRbk](https://avatars.discourse-cdn.com/v4/letter/n/82dd89/32.png) [@NickRbk](https://discuss.elastic.co/u/NickRbk)\
**Post date:** [July 30, 2018, 6:07pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/5 "2018-07-30T18:07:22Z")

</div>

You are right!  
Thank you a lot!

---

<div class="post-metadata">

**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 30, 2018, 6:10pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/6 "2018-07-30T18:10:30Z")

</div>

It took me a while to figure it out, but this can be done in the aggregate filter.

```
    aggregate {
        task_id => "%{product_id}"
        code => "
            map['product_id'] = event.get('product_id')
            map['product_name'] = event.get('product_name')

            map['properties'] ||= {}
            if map['properties'].has_key? event.get('property_name')
                map['properties'][event.get('property_name')] << event.get('property_value')
            else
                p = { event.get('property_name') => [event.get('property_value')] }
                map['properties'] = map['properties'].merge(p)
            end

            event.cancel()
        "
        push_previous_map_as_event => true
        timeout => 3
    }
```

---

<div class="post-metadata">

**Author:** ![NickRbk](https://avatars.discourse-cdn.com/v4/letter/n/82dd89/32.png) [@NickRbk](https://discuss.elastic.co/u/NickRbk)\
**Post date:** [July 30, 2018, 6:35pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/7 "2018-07-30T18:35:46Z")

</div>

Great solution!

---

<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:** [August 27, 2018, 6:35pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-nested-aggregation/142192/8 "2018-08-27T18:35:51Z")

</div>

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