# Need help with filter aggregate for nested elements

**URL:** https://discuss.elastic.co/t/need-help-with-filter-aggregate-for-nested-elements/103100
**Category:** Logstash
**Created:** [October 7, 2017, 4:07am UTC](https://discuss.elastic.co/t/need-help-with-filter-aggregate-for-nested-elements/103100 "2017-10-07T04:07:21Z")
**Posts on this page:** 2
**Page:** 1

<div class="post-metadata">

### Author: ![chrisgo](https://avatars.discourse-cdn.com/v4/letter/c/aca169/32.png) [@chrisgo](https://discuss.elastic.co/u/chrisgo)
#### Post date: [October 7, 2017, 4:07am UTC](https://discuss.elastic.co/t/need-help-with-filter-aggregate-for-nested-elements/103100/1 "2017-10-07T04:07:21Z")

</div>

Hi,

Been trying to use the aggregate filter to build nested objects. I think my config is correct but maybe I am running it wrong

This is my setup (Elasticsearch 5.6, Logstash 5.6, debian 9/stretch)

`orders`

| id | name | amount | date |
| --- | --- | --- | --- |
| 1 | Alpha | $100 | 2017-09-01 |
| 2 | Bravo | $200 | 2017-09-02 |
| 3 | Charlie | $300 | 2017-09-03 |

`order_events`

| id | order\_id | note | date |
| --- | --- | --- | --- |
| 1 | 1 | Created for Alpha | 2017-09-01 |
| 2 | 1 | Paid by Alpha using Visa | 2017-09-01 |
| 3 | 1 | Shipped to Alpha (Alabama) | 2017-09-01 |
| 4 | 2 | Created for Bravo | 2017-09-02 |
| 5 | 2 | Paid by Bravo using Mastercard | 2017-09-02 |
| 6 | 2 | Shipped to Bravo (Boise, Idaho) | 2017-09-02 |
| 7 | 3 | Created by Charlie | 2017-09-03 |
| 8 | 3 | Paid by Charlie using Amex | 2017-09-03 |
| 9 | 3 | Shipped to Charlie (California) | 2017-09-03 |

I have 2 conf files as follows (creates an index called `orders`)

```auto
// db_orders.conf
input {
  jdbc {
    jdbc_driver_library => "/home/vagrant/mysql-connector-java-5.1.44/mysql-connector-java-5.1.44-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.10.11:3306/example"
    jdbc_user => "root"
    jdbc_password => "password"
    statement => "
      SELECT
       *
      FROM
       orders
    "
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
  }
}

output {
  elasticsearch {
    index => "orders"
    document_type => "order"
    document_id => "%{id}"
    hosts => ["localhost"]
  }
}

```

```auto
// db_order_events.conf

input {
  jdbc {
    jdbc_driver_library => "/home/vagrant/mysql-connector-java-5.1.44/mysql-connector-java-5.1.44-bin.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.10.11:3306/example"
    jdbc_user => "root"
    jdbc_password => "password"
    statement => "
      SELECT
       *
      FROM
       order_events
    "
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
  }
}

filter {
  aggregate {
    task_id => "%{order_id}"
    code => "
      map['order_id'] = event.get('order_id')
      map['events'] ||= []
      map['events'] << {
        'id' => event.get('id'),
        'order_id' => event.get('order_id'),
        'note' => event.get('note')
      }
    "
    push_previous_map_as_event => true
    timeout => 3
  }
}

output {
  elasticsearch {
    index => "orders"
    document_type => "order"
    document_id => "%{id}"
    hosts => ["localhost"]
  }
}

```

and I run them separately \<= maybe this is the problem ???

```auto
sudo -Hu logstash /usr/share/logstash/bin/logstash --path.settings=/etc/logstash -f /etc/logstash/conf.d/db_orders.conf

sudo -Hu logstash /usr/share/logstash/bin/logstash --path.settings=/etc/logstash -f /etc/logstash/conf.d/db_order_events.conf

```

This is mostly based on following "Example #4" on the documentation

I have tried doing different variations inside the filter yml section with no luck

* * *

Doing a search

[http://192.168.10.14:9200/orders/\_search?q=](http://192.168.10.14:9200/orders/_search?q=) **Alpha** &pretty&pretty

shows my only a "flat" object without the nested notes

```auto
{
  "took" : 17,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 0.6173066,
    "hits" : [
      {
        "_index" : "orders",
        "_type" : "order",
        "_id" : "1",
        "_score" : 0.6173066,
        "_source" : {
          "date" : "2017-09-01",
          "name" : "Alpha",
          "id" : "1",
      }
    ]
  }
}

```

I was expecting it to do something like

```auto
...
      {
        "_index" : "orders",
        "_type" : "order",
        "_id" : "1",
        "_score" : 0.6173066,
        "_source" : {
          "date" : "2017-09-01",
          "name" : "Alpha",
          "id" : "1",
          "events": [
             { ... "note": "Created for Alpha" },
             { ... "note": "Paid by Alpha ... " },
             { ... "note": "Shipped to Alpha ... " },
          ]
      }
...

```

so the following search will work

[http://192.168.10.14:9200/orders/\_search?q=](http://192.168.10.14:9200/orders/_search?q=) **Visa** &pretty&pretty

and pull the same results above (instead of NO hits)

Am I missing something?

Thank you

---

<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: [November 4, 2017, 4:07am UTC](https://discuss.elastic.co/t/need-help-with-filter-aggregate-for-nested-elements/103100/2 "2017-11-04T04:07:23Z")

</div>

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