Need help with filter aggregate for nested elements


(Chris) #1

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)

// 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"]
  }
}
// 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 ???

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=Alpha&pretty&pretty

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

{
  "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

...
      {
        "_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=Visa&pretty&pretty

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

Am I missing something?

Thank you


(system) #2

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