SubDocument from a left outer join in JDBC plugin

[BenjaminD (Benjamin Dauvissat)]

Hi all,
I have a MySQL database with two tables linked : customer and invoice.
Of course, there can be many invoices for a given customer.

Using Logstash 7.1.1 and the JDBC input plugin, I try to load data in Elasticsearch 7.1.1.

My OS is Windows 10 Pro 64 bits

Here is my Logstash.conf file :

input {
  jdbc {
    jdbc_driver_library => "D:\dev\drivers\mysql-connector-java-5.1.47.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://192.168.14.1:3306/sales?useCursorFetch=true"
	jdbc_fetch_size => 50000
    jdbc_user => "xxxx"
	jdbc_password => "xxxx"
    schedule => "* * * * *"
    statement_filepath => "statement.sql"
	use_column_value => true
	tracking_column_type => "timestamp"
	tracking_column => "creation_date"
  }
}

output {
	elasticsearch {
		hosts => ["http://localhost:9200"]
		index => "jdbc"
		document_type => "sales"
		document_id => "%{id_customer}"
	}

}

And here is the statement.sql :

select cus.id_customer, cus.name, inv.creation_date, inv.label
from customer cus
left outer join invoice inv on inv.id_customer = cus.id_customer

What I expected is something like that :

"_source": {
    "id_customer": 1,
    "name": John,
	"invoices" : {
		"invoice" : {
			"creation_date": "2019-01-01 00:00:00.0000",
			"label": "First Invoice"
		},
		"invoice" : {
			"creation_date": "2019-12-31 00:00:00.0000",
			"label": "Second Invoice"
		},		
	}
}

What I get is :

"_source": {
    "id_customer": 1,
    "name": John,
	"creation_date": "2019-12-31 00:00:00.0000",
	"label": "Second Invoice"
}

How can I get what I need ?

Thanks for your help