How to group all the values for a column into an array jdbc mysql

Elastic Stack Logstash
1

Hi,

I am trying to import data from mysql to elasticsearch via logstash and i would like to know if it's possible to group all the values for a column into an array per id.

for example in mysql, results of query is

 '_id',   'name'       , 'tag' 
'1', 'Reparer PC', 'tec'
'1', 'Reparer PC', 'Urgent'
'1', 'Reparer PC', 'jdbc'

and i would like to create a unique document like this in elasticsearch.

id:1, task.name: 'Reparer PC', task.tag { tec, Urgent, jdbc}

logstash configuration

input {
    jdbc {
        # Postgres jdbc connection string to our database, mydb
        jdbc_connection_string => "jdbc:mysql://******:3306/demo_es"
        # The user we wish to execute our statement as
        jdbc_user => "admin"
        jdbc_password => "******"
        # The path to our downloaded jdbc driver
        jdbc_driver_library => "/root/elasticsearch-jdbc-1.7.1.0/lib/mysql-connector-java-5.1.33.jar"
        # The name of the driver class for Postgresql
        jdbc_driver_class => "com.mysql.jdbc.Driver"
        # our query
        statement => "select taches.id AS '_id', taches.name AS 'task.name', tags.name AS 'task.tags' from taches inner join tagstaches ON taches.id = tagstaches.idtache inner join tags on tagstaches.idtag = tags.idtags order by taches.id"
    }
}

output {
    elasticsearch {
        protocol => http
        index => "tasks"
        document_type => "task"
        document_id => "%{_id}"
        host => "localhost"
    }
}

This conf create a document where task.tag have only jdbc.

Thanks for your help,

Christophe

1 Like
2

Hi @clp,

Have you found a solution to your problem? I encountered the same problem where I would like to group the values for one column into an array per id.
It would even be better if there was functionality which allows to create a nested object such that you can add multiple columns per id.

Regards,
Daan