# How to join two index

**URL:** <https://discuss.elastic.co/t/how-to-join-two-index/274572>\
**Category:** Elasticsearch\
**Created:** [June 1, 2021, 5:16am UTC](https://discuss.elastic.co/t/how-to-join-two-index/274572 "2021-06-01T05:16:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jium](https://avatars.discourse-cdn.com/v4/letter/j/73ab20/32.png) [@jium](https://discuss.elastic.co/u/jium)\
**Post date:** [June 1, 2021, 5:16am UTC](https://discuss.elastic.co/t/how-to-join-two-index/274572/1 "2021-06-01T05:16:10Z")

</div>

Hi,

I want to join two indexes or merge them into one index.

One index is being collected from the apache log and  
the other from mysql.

I have to join the data through common key in two indexes and  
visualize it through Kibana.

Please tell me know how I can take it.

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [June 1, 2021, 5:35am UTC](https://discuss.elastic.co/t/how-to-join-two-index/274572/2 "2021-06-01T05:35:35Z")

</div>

If the data in MySQL is not also in elasticsearch, I'd use logstash with:

- Elasticsearch input
- Jdbc filter
- Elasticsearch output

If the data is all in elasticsearch, you coul use:

> **[Enrich your data | Elasticsearch Guide \[7.13\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/ingest-enriching-data.html)**

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [June 1, 2021, 6:20am UTC](https://discuss.elastic.co/t/how-to-join-two-index/274572/3 "2021-06-01T06:20:33Z")

</div>

I like to add another option: [transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html).

A transform in a nutshell is a task that runs aggregation queries and persists the result in an index. In order to join 2 indices you need compatible mappings for grouping, e.g. a key that is named the same way and has the same type. Note, this can be achieved with scripts or runtime fields, however if you want to run in it continuously at scale, it is advised to use proper ordinary mappings.

For the `group_by` use `terms` on the common key.

For the aggregation part you can choose between all kinds of aggregations. Simple numeric fields can use e.g. `min` or `max`. For more complicated cases you have to use a script again, some examples are given in this post:

> [@Join between two indices is possible in elasticsearch](https://discuss.elastic.co/t/join-between-two-indices-is-possible-in-elasticsearch/212170/4):
>
> in addition to the already given answers, I like to mention another possibility: You could "join" 2 indices using [transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) and create a new index which has the data of both. Scripting give you a lot of flexibility to do this. 2 related posts: It depends on what you want to do. We do not have the classical database join but we might be able to solve you usecase in a different way. Maybe you can describe what you want to do?

#### enrich vs. transform

If you wonder weather enrich or transform is the better choice:

Use enrich if 1 source is static or almost static, for example you want to expand an id to a name.

Use transform if all sources are dynamic and can arrive in different order. E.g. for distributed tracing in a larger system you can't say if the database lookup happens 1st or the access to the web server, or it might be interleaved. In such a case - and it sounds like yours - it is better to use transform.

LBNL I assume you want to join the data not just once. For this, have a look at the _continuous mode_ in transform.

---

<div class="post-metadata">

**Author:** ![jium](https://avatars.discourse-cdn.com/v4/letter/j/73ab20/32.png) [@jium](https://discuss.elastic.co/u/jium)\
**Post date:** [June 2, 2021, 2:21am UTC](https://discuss.elastic.co/t/how-to-join-two-index/274572/4 "2021-06-02T02:21:34Z")

</div>

Many thanks to you,  
I solved the problem through your solution.!

---

<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:** [June 30, 2021, 2:21am UTC](https://discuss.elastic.co/t/how-to-join-two-index/274572/5 "2021-06-30T02:21:41Z")

</div>

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