# Merging the data column wise from two different indexes **URL:** \ **Category:** Elasticsearch\ **Created:** [July 17, 2024, 4:29pm UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296 "2024-07-17T16:29:16Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\ **Post date:** [July 17, 2024, 4:29pm UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/1 "2024-07-17T16:29:16Z")
Hi, I am trying to merge two indexes column wise based on a common key from both the indices. I have tried logstash ruby code, transforms but nothing seems working to me. I am getting individual records having the columns containing either from index1 or index2 but not combined. Please help how i can achieve. Index1: { "\_index": "order\_details", "\_id": "xxxxxx", "\_version": 1, "\_score": 0, "\_source": { "amount": 1200, "shop": "shop\_12345", "@timestamp": "2024-07-17T15:55:46.258578035Z", "key": "12345678", "@version": "1", "source": "AMAZON" } }, { "\_index": "order\_details", "\_id": "xxxxxx", "\_version": 1, "\_score": 0, "\_source": { "amount": 500, "shop": "shop\_12345", "@timestamp": "2024-07-17T15:55:46.258578035Z", "key": "12345679", "@version": "1", "source": "AMAZON" } }, { "\_index": "order\_details", "\_id": "xxxxxx", "\_version": 1, "\_score": 0, "\_source": { "amount": 120, "shop": "shop\_12345", "@timestamp": "2024-07-17T15:55:46.258578035Z", "key": "12345680", "@version": "1", "source": "AMAZON" } } Index2: { "\_index": "overall\_shop", "\_id": "XXXXXXXX", "\_version": 1, "\_score": 0, "\_source": { "source": "FLIPKART", "Order Net Value": "2326", "shop": "shop\_12345", } } As i have same filed names in both the indexes fields like shop, source are to be named as shop1, source1 from index1 and shop2, source2 from index2. Merged index: { "\_index": "merged\_details", "\_id": "xxxxxx", "\_version": 1, "\_score": 0, "\_source": { "amount": 1200, "shop1": "shop\_12345", "@timestamp": "2024-07-17T15:55:46.258578035Z", "key": "12345678", "@version": "1", "source1": "AMAZON", "source2": "FLIPKART", "Order Net Value": "2326", "shop2": "shop\_12345" } }, { "\_index": "merged\_details", "\_id": "xxxxxx", "\_version": 1, "\_score": 0, "\_source": { "amount": 500, "shop1": "shop\_12345", "@timestamp": "2024-07-17T15:55:46.258578035Z", "key": "12345679", "@version": "1", "source1": "AMAZON", "source2": "FLIPKART", "Order Net Value": "2326", "shop2": "shop\_12345" } }, { "\_index": "merged\_details", "\_id": "xxxxxx", "\_version": 1, "\_score": 0, "\_source": { "amount": 120, "shop1": "shop\_12345", "@timestamp": "2024-07-17T15:55:46.258578035Z", "key": "12345680", "@version": "1", "source1": "AMAZON", "source2": "FLIPKART", "Order Net Value": "2326", "shop2": "shop\_12345" } } ---
**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:47pm UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/2 "2024-07-18T13:47:43Z")
In case it helps, I wrote some ideas in this discussion: [Need Help with Merging Data from Two Elasticsearch Indices - #3 by prashant1](https://discuss.elastic.co/t/need-help-with-merging-data-from-two-elasticsearch-indices/363343/3) ---
**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\ **Post date:** [July 18, 2024, 6:02pm UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/3 "2024-07-18T18:02:38Z")
Thankyou @dadoonet , Will try with elasticsearch filter plugin as you suggested. But till now, i have tried in logstash with aggregate filter in many ways but end up getting individual records from each index separately instead of getting single record with merged fields from both the indexes. ```auto input { elasticsearch { cloud_id => "xxxxxxxxxxxxxxxxxxxx" proxy => "xxxxxxxxxxxxxxx" index => "index1" query => '{"query": {"match_phrase": {"shop": "12345"}},"_source": ["Price","shop", "source"]}' ssl => true user => "xxxxxxxxxx" password => "xxxxxxxxxxxxx" } elasticsearch { cloud_id => "xxxxxxxxxxxxxxx" proxy => "xxxxxxxxxxxxxxxx" index => "index2" query => '{"query": {"term": {"shop": "12345"}},"_source": ["amount","shop"]}' ssl => true user => "xxxxxxxxxxxxx" password => "xxxxxxxxxxxxxxxxxx" } } filter { mutate { add_field => { "common_key" => "%{shop}" "Secondary" => "%{source}" "price" => "%{price}" "amount" => "%{amount}" } } # Aggregate data using the common key aggregate { task_id => "%{common_key}" code => " map['shop'] ||= event.get('common_key'); map['source'] ||= event.get('source'); map['amount'] ||= event.get('amount'); map['price'] ||= event.get('price'); " push_map_as_event_on_timeout => true timeout_task_id_field => "common_key" timeout => 60 # Adjust timeout as per your requirement } } output { stdout { codec => rubydebug } elasticsearch { cloud_id => "xxxxxxxxxxxx" proxy => "xxxxxxxxxxxx" index => "index3" ssl => true user => "xxxxxxxxxxxx" password => "xxxxxxxxxxx" action => "create" } } ``` Output I am getting: ```auto Doc1: price shop source --------- --------- ------------- 50 12345 amazon Doc2: shop amount --------- ------------- 12345 400 ``` ---
**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, 8:42pm UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/4 "2024-07-18T20:42:05Z")
Yeah. That won't work. Use an input plus a filter instead of 2 inputs. ---
**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\ **Post date:** [July 19, 2024, 7:31am UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/5 "2024-07-19T07:31:34Z")
Hi @dadoonet, Thank you so much. The solution worked by using input elasticsearch filter and using a elastic filter for look up in the logstash filter section. I need to now calculate the sum of the price for each shop and add it into the index3 as a field. Could you please help how i can achieve this in the same logstash pipeline. ```auto Source1 price total_price shop_id1 source2 shop_id2 amount AMAZON 234 337 12345 FLIPKART 12345 400 AMAZON 44 337 12345 FLIPKART 12345 400 AMAZON 6 337 12345 FLIPKART 12345 400 ``` ---
**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 19, 2024, 9:33am UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/6 "2024-07-19T09:33:35Z")
As this one is solved, could you open a new discussion in #Logstash? I think it's the best place for it. ---
**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\ **Post date:** [July 19, 2024, 10:07am UTC](https://discuss.elastic.co/t/merging-the-data-column-wise-from-two-different-indexes/363296/7 "2024-07-19T10:07:00Z")
Thank you @dadoonet , I have created a new topic for this. [Calculating the Sum of a field by grouping](https://discuss.elastic.co/t/calculating-the-sum-of-a-field-by-grouping/363411)