# SubDocument from a left outer join in JDBC plugin

**URL:** <https://discuss.elastic.co/t/subdocument-from-a-left-outer-join-in-jdbc-plugin/184769>\
**Category:** Logstash\
**Created:** [June 7, 2019, 1:53pm UTC](https://discuss.elastic.co/t/subdocument-from-a-left-outer-join-in-jdbc-plugin/184769 "2019-06-07T13:53:06Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![BenjaminD](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/benjamind/32/4866_2.png) [@BenjaminD](https://discuss.elastic.co/u/BenjaminD)\
**Post date:** [June 7, 2019, 1:53pm UTC](https://discuss.elastic.co/t/subdocument-from-a-left-outer-join-in-jdbc-plugin/184769/1 "2019-06-07T13:53:06Z")

</div>

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

---

<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:** [July 5, 2019, 1:53pm UTC](https://discuss.elastic.co/t/subdocument-from-a-left-outer-join-in-jdbc-plugin/184769/2 "2019-07-05T13:53:14Z")

</div>

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