# Joins in ElasticSearch
**URL:** https://discuss.elastic.co/t/joins-in-elasticsearch/108360
**Category:** Elasticsearch
**Created:** [November 20, 2017, 10:27am UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360 "2017-11-20T10:27:59Z")
**Posts on this page:** 8
**Page:** 1
### Author:  [@devil\_srj7](https://discuss.elastic.co/u/devil_srj7)
#### Post date: [November 20, 2017, 10:27am UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/1 "2017-11-20T10:27:59Z")
Now i need to create parent-child relation in my documents.
There are 2 tables in MySQL.
```
Employee
+------+-------------+--------+------+
| id | name | salary | age |
+------+-------------+--------+------+
| 0 | Dxgow Xkiuq | 40414 | 33 |
| 1 | Kouni Pecre | 44814 | 41 |
| 2 | Jtwfl Peiyw | 76611 | 37 |
| 3 | Mnpxt Cpprc | 32538 | 31 |
| 4 | Ukhey Uosqg | 90272 | 40 |
| 5 | Eavcg Gqfoo | 43957 | 48 |
| 6 | Bcllu Vhara | 42928 | 32 |
| 7 | Bafna Qeymj | 91398 | 23 |
| 8 | Qxwni Tgwdw | 60973 | 31 |
| 9 | Jpcqc Qtyum | 54696 | 41 |
+------+-------------+--------+------+
Age
+--------------+
| employee_age |
+--------------+
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
+--------------+
```
Now while importing this data to elasticsearch via logstash i need to create parent child relationship between these two tables as AGE being the parent and Employee being child.
Similarly to have parent-child relationship to the data which is already in the index.
These are the 2 situation where I am stuck.
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [November 20, 2017, 10:29am UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/2 "2017-11-20T10:29:32Z")
Why do you need to do this?
---
### Author:  [@devil\_srj7](https://discuss.elastic.co/u/devil_srj7)
#### Post date: [November 20, 2017, 10:31am UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/3 "2017-11-20T10:31:06Z")
> [@warkolm](#):
>
> Why do you need to do this?
For performing analysis.
---
### Author:  [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)
#### Post date: [November 20, 2017, 12:12pm UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/4 "2017-11-20T12:12:04Z")
From your example, all of the data on the "Age" table is already available on the "Employee" table.
Putting technical details aside, what is the business question you are trying to answer?
---
### Author:  [@devil\_srj7](https://discuss.elastic.co/u/devil_srj7)
#### Post date: [November 29, 2017, 12:14pm UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/5 "2017-11-29T12:14:55Z")
**I have a large dataset of public welfare department in MySQL database.**
**Which has 7-8 tables and each tables has 40-50 columns.**
**So I need to perform joins in elasticsearch while migrating the data form RDMS to ES through logstash.**
**As I dont want to widen the column for redundancy and performance issue.**
> [@devil\_srj7](#):
>
> Now i need to create parent-child relation in my documents.
>
> There are 2 tables in MySQL.
>
> Employee
> +------+-------------+--------+------+
> | id | name | salary | age |
> +------+-------------+--------+------+
> | 0 | Dxgow Xkiuq | 40414 | 33 |
> | 1 | Kouni Pecre | 44814 | 41 |
> | 2 | Jtwfl Peiyw | 76611 | 37 |
> | 3 | Mnpxt Cpprc | 32538 | 31 |
> | 4 | Ukhey Uosqg | 90272 | 40 |
> | 5 | Eavcg Gqfoo | 43957 | 48 |
> | 6 | Bcllu Vhara | 42928 | 32 |
> | 7 | Bafna Qeymj | 91398 | 23 |
> | 8 | Qxwni Tgwdw | 60973 | 31 |
> | 9 | Jpcqc Qtyum | 54696 | 41 |
> +------+-------------+--------+------+
>
> Age
> +--------------+
> | employee\_age |
> +--------------+
> | 20 |
> | 21 |
> | 22 |
> | 23 |
> | 24 |
> | 25 |
> | 26 |
> | 27 |
> | 28 |
> | 29 |
> +--------------+
>
> Now while importing this data to elasticsearch via logstash i need to create parent child relationship between these two tables as AGE being the parent and Employee being child.
>
> Similarly to have parent-child relationship to the data which is already in the index.
>
> These are the 2 situation where I am stuck.
This was an example that i was trying to work on first than directly implementing on my main data.
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [November 29, 2017, 12:43pm UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/6 "2017-11-29T12:43:38Z")
Do joins at index time. Specifically if you want it to be fast as you asked for.
Redundancy is not an issue IMO.
Here is a blog post I wrote about connecting sql to nosql: [http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/](http://david.pilato.fr/blog/2015/05/09/advanced-search-for-your-legacy-application/)
---
### Author:  [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)
#### Post date: [November 30, 2017, 9:05am UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/7 "2017-11-30T09:05:49Z")
> [@devil\_srj7](#):
>
> i need to create parent child relationship between these two tables as AGE being the parent and Employee being child.
I still don't get it. Normally a join uses a key to gain access to more values. Here there is only one value (age) and the key is the same as the value. Again, I would ask:
> Putting technical details aside, what is the business question you are trying to answer?
An example question might be:
> What is the average age of people with salaries \> 100k?
etc.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 28, 2017, 9:06am UTC](https://discuss.elastic.co/t/joins-in-elasticsearch/108360/8 "2017-12-28T09:06:05Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.