# How to join 2 index using 2 common values

**URL:** <https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248>\
**Category:** Elasticsearch\
**Created:** [May 26, 2020, 6:32am UTC](https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248 "2020-05-26T06:32:47Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![aannee](https://avatars.discourse-cdn.com/v4/letter/a/87869e/32.png) [@aannee](https://discuss.elastic.co/u/aannee)\
**Post date:** [May 26, 2020, 6:32am UTC](https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248/1 "2020-05-26T06:32:47Z")

</div>

Hi, is it possible to join 2 index in elasticsearch? Maybe using query. Or I'm thinking if I could do a logstash pipeline that will query the two index and join them inside the pipeline.

First time to face a request like this. So, I don't know if this is possible or not.  
Thank you!

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [May 26, 2020, 6:38am UTC](https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248/2 "2020-05-26T06:38:38Z")

</div>

You do need to use something external to query both indices and join them. There's nothing Elasticsearch can do for you on that front sorry.

---

<div class="post-metadata">

**Author:** ![aannee](https://avatars.discourse-cdn.com/v4/letter/a/87869e/32.png) [@aannee](https://discuss.elastic.co/u/aannee)\
**Post date:** [May 26, 2020, 7:00am UTC](https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248/3 "2020-05-26T07:00:26Z")

</div>

What I'm doing now is a logstash pipeline that queries the two indices. But the result is like this.

Just an example

```auto
message => {
  "took": ..........
}
{
   "took": ..........
}

```

As u can see, the result is in the message where in the result of the another index is appended in the first index's result.

I'm wondering if I can split this two result. And after that maybe do the filter split json. And will try a ruby code to join it.

Hehe, but I think its not possible?

Will explore more. Thank for replying!

---

<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:** [May 26, 2020, 10:19am UTC](https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248/4 "2020-05-26T10:19:55Z")

</div>

With [transform](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html) you can query 2 or more indices and if they have a common field, like an id, you can "join" the results by grouping the common id.

To combine the documents you can use a scripted metric aggregation. It's possible to "collapse" the other documents, which means creating an array of all source documents, or you "join" them, meaning one overwrite the other one. For the later you have to decide the order.

Example post:

> [@Combine multiple document into one document with limited Fields ( merging of documents )](https://discuss.elastic.co/t/combine-multiple-document-into-one-document-with-limited-fields-merging-of-documents/231758/6):
>
> You can use a [transform](https://www.elastic.co/guide/en/elasticsearch/reference/7.7/transforms.html) for it. The group\_by must be based on a common criteria, e.g. a common id. For the combining step, you can use a scripted metric, e.g.: "all\_docs": { "scripted\_metric": { "init\_script": "state.docs = []", "map\_script": "state.docs.add(new HashMap(params['\_source']))", "combine\_script": "return state.docs", "reduce\_script": "def docs = []; for (s in states) {for (d in s) { docs.add(d);}}return docs" } } this would create an array with the original do…

---

<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 23, 2020, 10:19am UTC](https://discuss.elastic.co/t/how-to-join-2-index-using-2-common-values/234248/5 "2020-06-23T10:19:58Z")

</div>

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