# Migrate mysql nth level data to elasticsearch via logstash (config file)

**URL:** <https://discuss.elastic.co/t/migrate-mysql-nth-level-data-to-elasticsearch-via-logstash-config-file/202833>\
**Category:** Logstash\
**Created:** [October 9, 2019, 1:09pm UTC](https://discuss.elastic.co/t/migrate-mysql-nth-level-data-to-elasticsearch-via-logstash-config-file/202833 "2019-10-09T13:09:11Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![saifrehman](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/saifrehman/32/56971_2.png) [@saifrehman](https://discuss.elastic.co/u/saifrehman)\
**Post date:** [October 9, 2019, 1:09pm UTC](https://discuss.elastic.co/t/migrate-mysql-nth-level-data-to-elasticsearch-via-logstash-config-file/202833/1 "2019-10-09T13:09:11Z")

</div>

I am migrating mysql data to elasticsearch via logstash. The database is too big and have lots of relations. I already imported the simple tables and table with nested data(single level parent/child) as well.

**Problem** : I have user, post and comments table. I want all three table into one type as follow.

```auto
> {
> "user_id" : 200,
> "user_name" : "john doe",
> "posts" : [
> {
> post_id : 1,
> post_title : "post description",
> "comments" : [
> {
> "comment_id" : 1
> "comment_text" : "good post"
> },
> { "comment_id" : 2
> "comment_text" : "awsome post"
> }
> ]
> },
> {
> post_id : 2,
> post_title : "post description",
> "comments" : [
> {
> "comment_id" : 3
> "comment_text" : "good post"
> },
> { "comment_id" : 4
> "comment_text" : "awsome post"
> }
> ]
> }
> ]
> }

I also want it to be 3 level nested object. if a user upload a pic in comments then that media is a nested object under comments.
```

---

<div class="post-metadata">

**Author:** ![saifrehman](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/saifrehman/32/56971_2.png) [@saifrehman](https://discuss.elastic.co/u/saifrehman)\
**Post date:** [October 10, 2019, 10:35am UTC](https://discuss.elastic.co/t/migrate-mysql-nth-level-data-to-elasticsearch-via-logstash-config-file/202833/2 "2019-10-10T10:35:15Z")

</div>

`I have done this by spending two days. here is my config file.`

> input {  
> jdbc{  
> jdbc\_validate\_connection =\> true  
> jdbc\_connection\_string =\> "jdbc:mysql://172.17.0.2:3306/dbname"  
> jdbc\_user =\> "username"  
> jdbc\_password =\> "password"  
> jdbc\_driver\_library =\> "/home/ilsa/mysql-connector-java-5.1.36-bin.jar"  
> jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"  
> statement =\> "your query that contain joins "  
> }  
> }  
> filter {  
> aggregate {  
> task\_id =\> "%{user\_id}"  
> code =\> "  
> map['user\_id'] = event.get('user\_id')  
> map['email'] = event.get('email')  
> map['username'] = event.get('username')  
> map['posts'] ||=   
> map['posts'] \<\< {  
> 'post\_id' =\> event.get('post\_id'),  
> 'content' =\> event.get('content'),  
> 'comments' =\> \<\< {  
> 'comment\_id' =\> event.get('comment\_id'),  
> 'comment\_post\_id' =\> event.get('comment\_post\_id'),  
> 'comment\_text' =\> event.get('comment\_text')  
> }  
> }  
> event.cancel()"  
> push\_previous\_map\_as\_event =\> true  
> timeout =\> 30  
> }  
> }  
> output {  
> stdout{ codec =\> rubydebug }  
> elasticsearch{  
> action =\> "index"  
> index =\> "index\_name"  
> document\_type =\> "\_doc"  
> document\_id =\> "%{user\_id}"  
> hosts =\> "localhost:9200"  
> }  
> }

---

<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 7, 2019, 10:35am UTC](https://discuss.elastic.co/t/migrate-mysql-nth-level-data-to-elasticsearch-via-logstash-config-file/202833/3 "2019-11-07T10:35:19Z")

</div>

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