Need Help with Merging Data from Two Elasticsearch Indices

Elastic Stack Elasticsearch
1

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:

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

Index test2:

information   net_price_value
-------------         --------------
11                           52

Desired Output:

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!

2

That sounds like a good candidate for an enrich pipeline.

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

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

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 ?

4

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

Yes probably. You would need to create a new index. Like reading the index test1 with an elasticsearch input plugin, then doing lookups in index2 with an elasticsearch filter plugin and then write the result to index3 with an elasticsearch output plugin.

I wrote a post about this at Enrich your Elasticsearch documents with Logstash | Elastic Blog

5

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