# Need Help with Merging Data from Two Elasticsearch Indices

**URL:** <https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343>\
**Category:** Elasticsearch\
**Created:** [July 18, 2024, 11:10am UTC](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343 "2024-07-18T11:10:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![prashant1](https://avatars.discourse-cdn.com/v4/letter/p/bc8723/32.png) [@prashant1](https://discuss.elastic.co/u/prashant1)\
**Post date:** [July 18, 2024, 11:10am UTC](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343/1 "2024-07-18T11:10:53Z")

</div>

Hi,

I need to merge data from two indices (`test1` and `test2`) based on a common key (`information`). Here’s a simplified example of my indices:

**Index `test1`:**

```auto
sample_id information price
--------- ------- ----------------
test1 11 52
test2 11 53
test3 11 54

```

**Index `test2`:**

```auto
information net_price_value
------------- --------------
11 52

```

Desired Output:

```auto
sample_id information price net_price_value
-------------------------------------------------
test1 11 52 52
test2 11 53 52
test3 11 54 52

```

I've explored using the Elasticsearch Transform API (`_transform`) with scripted metrics, but I'm encountering challenges in correctly combining and mapping data from both indices into a single transformed index (`test3`). Could someone please advise on the best approach or provide a sample query to achieve this? Any insights or examples would be greatly appreciated.

Thank you in advance!

---

<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:** [July 18, 2024, 11:28am UTC](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343/2 "2024-07-18T11:28:41Z")

</div>

That sounds like a good candidate for an [enrich pipeline](https://www.elastic.co/guide/en/elasticsearch/reference/current/ingest-enriching-data.html).

And then, the usage with `ES|QL` is much easier:

```auto
from test1
| enrich test-policy on information with net_price_value
| where weight > 80
| keep sample_id, information, price, net_price_value
| limit 10

```

---

<div class="post-metadata">

**Author:** ![prashant1](https://avatars.discourse-cdn.com/v4/letter/p/bc8723/32.png) [@prashant1](https://discuss.elastic.co/u/prashant1)\
**Post date:** [July 18, 2024, 12:39pm UTC](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343/3 "2024-07-18T12:39:10Z")

</div>

Thanks @dadoonet for the quick response.

Is there anyway to achieve it using transform or logstash as I have access restrictions to work on the enrich policies ?

---

<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:** [July 18, 2024, 1:26pm UTC](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343/4 "2024-07-18T13:26:38Z")

</div>

> [@prashant1](#):
>
> Is there anyway to achieve it using transform

I don't think it's made for that use case.

> [@prashant1](#):
>
> Is there anyway to achieve it using [...] logstash

Yes probably. You would need to create a new index. Like reading the index test1 with an [elasticsearch input plugin](https://www.elastic.co/guide/en/logstash/current/plugins-inputs-elasticsearch.html), then doing lookups in index2 with an [elasticsearch filter plugin](https://www.elastic.co/guide/en/logstash/current/plugins-filters-elasticsearch.html) and then write the result to index3 with an [elasticsearch output plugin](https://www.elastic.co/guide/en/logstash/current/plugins-outputs-elasticsearch.html).

I wrote a post about this at [Enrich your Elasticsearch documents with Logstash | Elastic Blog](https://www.elastic.co/blog/enrich-your-elasticsearch-documents-with-logstash/)

---

<div class="post-metadata">

**Author:** ![prashant1](https://avatars.discourse-cdn.com/v4/letter/p/bc8723/32.png) [@prashant1](https://discuss.elastic.co/u/prashant1)\
**Post date:** [July 19, 2024, 7:32am UTC](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343/5 "2024-07-19T07:32:15Z")

</div>

Thanks @dadoonet for help  
We are able to achieve the usecase through elasticsearch filter plugin.