# Import one-to-many relation from SQL

**URL:** <https://discuss.elastic.co/t/import-one-to-many-relation-from-sql/83375>\
**Category:** Logstash\
**Created:** [April 24, 2017, 8:14am UTC](https://discuss.elastic.co/t/import-one-to-many-relation-from-sql/83375 "2017-04-24T08:14:49Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![DOB](https://avatars.discourse-cdn.com/v4/letter/d/b2d939/32.png) [@DOB](https://discuss.elastic.co/u/DOB)\
**Post date:** [April 24, 2017, 8:14am UTC](https://discuss.elastic.co/t/import-one-to-many-relation-from-sql/83375/1 "2017-04-24T08:14:49Z")

</div>

I try to import job ads form two MySQL tables (job data and locations) but I'm facing a problem when a job ad has multiple locations. I'm using this MySQL query:

> SELECT id, company, jobtitle, description, priority, DATE\_FORMAT(date, '%Y-%m-%d %T') AS date, sa\_locations.location AS location\_name, sa\_locations.lat AS location\_lat, sa\_locations.lon AS location\_lon  
> FROM sa\_data JOIN sa\_locations  
> ON sa\_data.id = sa\_locations.id  
> ORDER BY id

Ignoring the location problem everything is ok, and I receive results like this:

> ```
> {
> "_index" : "jk",
> "_type" : "jobposting",
> "_id" : "26362",
> "_score" : 1.0,
> "_source" : {
> "date" : "2017-04-22 00:00:00",
> "location_name" : "Berlin",
> "location_lat" : "52.520007",
> "location_lon" : "13.404954",
> "@timestamp" : "2017-04-24T07:50:31.660Z",
> "@version" : "1",
> "description" : "Some text here",
> "company" : "Test Company",
> "id" : 26362,
> "jobtitle" : "Architect Data Center Network & Security",
> "priority" : 10,
> }
> },
> {
> "_index" : "jk",
> "_type" : "jobposting",
> "_id" : "26363",
> "_score" : 1.0,
> "_source" : {
> "date" : "2017-04-22 00:00:00",
> "location_name" : "Hamburg",
> "location_lat" : "53.551085",
> "location_lon" : "9.993682",
> "@timestamp" : "2017-04-24T07:50:31.660Z",
> "@version" : "1",
> "description" : "Some text here",
> "company" : "Test Company",
> "id" : 26363,
> "jobtitle" : "Architect Data Center Network & Security",
> "priority" : 10,
> }
> }
> 
> ```

What I'm trying to get is something like this:

> ```
> {
> "_index" : "jk",
> "_type" : "jobposting",
> "_id" : "26362",
> "_score" : 1.0,
> "_source" : {
> "date" : "2017-04-22 00:00:00",
> "locations" : [{ "name": "Berlin", "lat" : "52.520007", "lon" : "13.404954" }, { "name": "Hamburg", "lat" : "53.551085", "lon" : "9.993682" }]
> "@timestamp" : "2017-04-24T07:50:31.660Z",
> "@version" : "1",
> "description" : "Some text here",
> "company" : "Test Company",
> "id" : 26362,
> "jobtitle" : "Architect Data Center Network & Security",
> "priority" : 10,
> }
> }
> 
> ```

So that if I'm going to search for jobs near Berlin or Hamburg by using a geo\_distance filter this job should appear. Is there any way to import data in that way with logstash?

My logstash.conf looks like this:

> input {  
> jdbc {  
> jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/jk"  
> jdbc\_user =\> "..."  
> jdbc\_password =\> "..."  
> jdbc\_driver\_library =\> "/etc/logstash/mysql-connector-java-5.1.41/mysql-connector-java-5.1.41-bin.jar"  
> jdbc\_driver\_class =\> "com.mysql.jdbc.Driver"  
> statement =\> "SELECT id, company, jobtitle, description, priority, DATE\_FORMAT(date, '%Y-%m-%d %T') AS date, sa\_locations.location AS location\_name, sa\_locations.lat AS location\_lat, sa\_locations.lon AS location\_lon  
> FROM sa\_data JOIN sa\_locations  
> ON sa\_data.id = sa\_locations.id  
> ORDER BY id  
> }  
> }

> #filter {  
> # aggregate {  
> # task\_id =\> "%{id}"  
> # code =\> "  
> # map['location\_name'] = event.get('location\_name')  
> # map['location\_lat'] = event.get('location\_lat')  
> # map['location\_lon'] = event.get('location\_lon')  
> # map['locations'] ||=   
> # map['locations'] \<\< {'location\_name' =\> event.get('location\_name')}  
> # map['locations'] \<\< {'location\_lat' =\> event.get('location\_lat')}  
> # map['locations'] \<\< {'location\_lon' =\> event.get('location\_lon')}  
> # event.cancel()  
> # "  
> # push\_previous\_map\_as\_event =\> true  
> # timeout =\> 3  
> # }  
> #}

> output {  
> elasticsearch {  
> index =\> "jk"  
> document\_type =\> "jobposting"  
> document\_id =\> "%{id}"  
> hosts =\> ["localhost:9200"]  
> }  
> }

The filter seemed to be a wrong approach.

---

<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:** [May 22, 2017, 8:27am UTC](https://discuss.elastic.co/t/import-one-to-many-relation-from-sql/83375/2 "2017-05-22T08:27:33Z")

</div>

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