# Aggregate filter plugin - problem how to construct the filter

**URL:** <https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690>\
**Category:** Logstash\
**Created:** [January 27, 2020, 3:51pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690 "2020-01-27T15:51:30Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![skjoos](https://avatars.discourse-cdn.com/v4/letter/s/8797f3/32.png) [@skjoos](https://discuss.elastic.co/u/skjoos)\
**Post date:** [January 27, 2020, 3:51pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/1 "2020-01-27T15:51:31Z")

</div>

Hello!

I'm trying to aggregate some data from a MS SQL database by using Logstash.  
Below is a view of the database.

 ![Table](https://us1.discourse-cdn.com/elastic/original/3X/4/d/4dad9e9b15e004cba0b246273d5b5e071ff92e38.png)

The jbdc field and my aggregation filter look like this:

```auto
jdbc { 
             ....
            statement => 
            "
                select 
                d.Id,
                u.FirstName
                from Document d
                full join Approvers a
                on d.Id = a.DocumentId
                full join [User] u
                on a.UserId = u.Id
          "
   }
   filter {
       aggregate {
           task_id => "%{d.Id}"
           code => "
               map['document_id'] || = event.get('d.Id')
               map['approvers'] ||= []  
               map['approvers'] << {'first_name' => event.get('u.FirstName')}                                      
               event.cancel()
           "
          push_previous_map_as_event => true
          timeout => 3
       }
   }

```

If I execute the statment inside the jbdc field above in the database i would get this data:

```auto
| Id | FirstName |
| 1 | Nils |
| 2 | Rudolf |
| 2 | Olle |
| 3 | NULL |

```

And the way I would like to aggregate the data look something like this:

```json
[
    {
        "document_id": "1",
        "approvers": [
            { "first_name": "Nils"}
         ]
    },
    {
        "document_id": "2",
        "approvers": [
            { "first_name": "Rudolf"},
            { "first_name": "Olle"}
         ]
    },
    {
        "document_id": "3",
        "approvers": []
    },
]

```

Any clue on how to fix it? 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:** [January 27, 2020, 4:23pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/2 "2020-01-27T16:23:05Z")

</div>

> [@skjoos](#):
>
> "%{ d.Id}"

Why do you have that space inside the {} ?

---

<div class="post-metadata">

**Author:** ![skjoos](https://avatars.discourse-cdn.com/v4/letter/s/8797f3/32.png) [@skjoos](https://discuss.elastic.co/u/skjoos)\
**Post date:** [January 28, 2020, 9:11am UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/3 "2020-01-28T09:11:58Z")

</div>

@Badger Just a typo for this example, fixed now.  
Still got the same error.

---

<div class="post-metadata">

**Author:** ![ITIC](https://avatars.discourse-cdn.com/v4/letter/i/90ced4/32.png) [@ITIC](https://discuss.elastic.co/u/ITIC)\
**Post date:** [January 28, 2020, 10:15am UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/4 "2020-01-28T10:15:56Z")

</div>

Hi

What do you get if you run your config? Can you show us the actual output you are getting from `stdout{}`?

Are you getting any error messsages in your logs? Or is it just that the output you get is not what you need?

Have you made sure your pipeline has only one worker? This is a requirement for the `aggregate{}` filter.

Hope this helps.

---

<div class="post-metadata">

**Author:** ![skjoos](https://avatars.discourse-cdn.com/v4/letter/s/8797f3/32.png) [@skjoos](https://discuss.elastic.co/u/skjoos)\
**Post date:** [January 28, 2020, 1:33pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/5 "2020-01-28T13:33:41Z")

</div>

Hi

I get no errors when running the config and the config runs with **-w 1**.  
It feels like the filter isn't doing anything at all. Does Logstash recognize fields with a dot like Document.Id **/** d.Id?  
One thing I'm really unsure about is the **task\_id** , what id should I use when using joins?

The result is below:

> **1 hit**
>
> ```auto
> {
> "_index": "randomindex",
> "_type": "_doc",
> "_id": "OFpE7G8BDQZMTmIc_gTh",
> "_score": 1,
> "_source": {
> "@version": "1",
> "first_name": "Nils",
> "document_id": 1,
> "@timestamp": "2020-01-28T13:09:02.005Z"
> },
> "fields": {
> "@timestamp": [
> "2020-01-28T13:09:02.005Z"
> ]
> }
> }
> 
> ```

> **2 hit**
>
> ```auto
> {
> "_index": "randomindex",
> "_type": "_doc",
> "_id": "OVpE7G8BDQZMTmIc_gTh",
> "_score": 1,
> "_source": {
> "@version": "1",
> "first_name": "Olle",
> "document_id": 2,
> "@timestamp": "2020-01-28T13:09:01.994Z"
> },
> "fields": {
> "@timestamp": [
> "2020-01-28T13:09:01.994Z"
> ]
> }
> }
> 
> ```

> **3 hit**
>
> ```auto
> {
> "_index": "randomindex",
> "_type": "_doc",
> "_id": "N1pE7G8BDQZMTmIc_gTh",
> "_score": 1,
> "_source": {
> "@version": "1",
> "first_name": null,
> "document_id": 3,
> "@timestamp": "2020-01-28T13:09:02.006Z"
> },
> "fields": {
> "@timestamp": [
> "2020-01-28T13:09:02.006Z"
> ]
> }
> }
> 
> ```

> **4 hit**
>
> ```auto
> {
> "_index": "randomindex",
> "_type": "_doc",
> "_id": "OlpE7G8BDQZMTmIc_wRi",
> "_version": 1,
> "_score": 0,
> "_source": {
> "@version": "1",
> "first_name": "Rudolf",
> "document_id": 2,
> "@timestamp": "2020-01-28T13:09:02.005Z"
> },
> "fields": {
> "@timestamp": [
> "2020-01-28T13:09:02.005Z"
> ]
> }
> }
> 
> ```

---

<div class="post-metadata">

**Author:** ![ITIC](https://avatars.discourse-cdn.com/v4/letter/i/90ced4/32.png) [@ITIC](https://discuss.elastic.co/u/ITIC)\
**Post date:** [January 28, 2020, 2:10pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/6 "2020-01-28T14:10:04Z")

</div>

Hi

I think you should use `document_id` as `task_id` in your `aggregate{}` filter (task\_id =\> "%{document\_id}").

Try commenting out the `code` section for now, or leaving it empty (`code => ""`).

Your `timeout` is only 3 seconds. Try increasing it.

Since you do not have beginning or end events, you should probably use `timeout_task_id_field => "task_id"` and set a value to [`inactivity_timeout`](https://www.elastic.co/guide/en/logstash/current/plugins-filters-aggregate.html#plugins-filters-aggregate-inactivity_timeout).

Hope this helps

---

<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:** [January 28, 2020, 2:59pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/7 "2020-01-28T14:59:01Z")

</div>

In addition to '-w 1' you will need to disable [pipeline.java\_execution](https://github.com/elastic/logstash/issues/10938).

---

<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:** [February 25, 2020, 2:59pm UTC](https://discuss.elastic.co/t/aggregate-filter-plugin-problem-how-to-construct-the-filter/216690/8 "2020-02-25T14:59:04Z")

</div>

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