# Merge 2 index based off fields and not rows **URL:** \ **Category:** Kibana\ **Created:** [December 22, 2021, 3:15pm UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706 "2021-12-22T15:15:24Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Sugand](https://avatars.discourse-cdn.com/v4/letter/s/9d8465/32.png) [@Sugand](https://discuss.elastic.co/u/Sugand)\ **Post date:** [December 22, 2021, 3:15pm UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/1 "2021-12-22T15:15:24Z")
Hi ES team, I'm looking to merge 2 index's based off unique ID which is same across both the index and the desired index should look like, Index 1 Fields ID, Email, Fax Index 2 Fields ID, Email, Fax Now the merged Index should be, Index 3 ID, Index1\_Email, Index2\_Email, Index1\_Fax, Index2\_ Fax Hence the merge has taken place by field and not rows. Appreciate your input's. ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [December 22, 2021, 3:17pm UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/2 "2021-12-22T15:17:23Z")
you can do this via python. how big your index1 and index2 (how many records?) use elasticsearch\_dsl to scan ID create new record and push that to ELK. ---
**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:** [December 22, 2021, 3:27pm UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/3 "2021-12-22T15:27:35Z")
Have a look at transforms, you can source both indexes, use a `pivot`, group on `terms` using the field `id`. For the output fields you have several options (just ideas, not verified): - a `filter` aggregation and `top_metrics` as sub aggregation - `top_metrics` on top, sort by `_index` ascending for index 1, descending to get index 2 - `scripted_metric` You find further information in older posts: [How to join two index - #3 by Hendrik\_Muhs](https://discuss.elastic.co/t/how-to-join-two-index/274572/3) Note however that these posts are old and e.g. `top_metrics` has been added just recently. I talk about `top_metrics` in a recent advent post: [Dec 11th, 2021: [en] On a road trip with Transform](https://discuss.elastic.co/t/dec-11th-2021-en-on-a-road-trip-with-transform/290532) ---
**Author:** ![Sugand](https://avatars.discourse-cdn.com/v4/letter/s/9d8465/32.png) [@Sugand](https://discuss.elastic.co/u/Sugand)\ **Post date:** [December 23, 2021, 10:55am UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/4 "2021-12-23T10:55:13Z")
Thanks @Hendrik_Muhs , But the issue I seem to be stuck with an error as shown below. ![twne](https://us1.discourse-cdn.com/elastic/original/3X/b/6/b69e8a416dea5241d1f2d646c40d23f86182a4ba.jpeg) Appreciate your help on the same. ---
**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:** [December 23, 2021, 5:26pm UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/5 "2021-12-23T17:26:49Z")
Your call does not look correct to me, can you delete the id from the body and don't use the underscore in the path? ```auto PUT _transform/index_merge { ... } ``` The id (`index_merge`) must be specified as part of the path. You can specify it in the body, too, but than they must match. Leading underscores are not allowed. ---
**Author:** ![Sugand](https://avatars.discourse-cdn.com/v4/letter/s/9d8465/32.png) [@Sugand](https://discuss.elastic.co/u/Sugand)\ **Post date:** [December 24, 2021, 11:42am UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/6 "2021-12-24T11:42:28Z")
![11](https://us1.discourse-cdn.com/elastic/original/3X/e/a/ea12eab562588c66fd3bc15e5b86811b861e74d6.jpeg) Hi @Hendrik_Muhs , not sure if still I'm missing something. Thanks, ---
**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:** [December 27, 2021, 4:25pm UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/7 "2021-12-27T16:25:20Z")
Can you provide the version and environment you are using? It seems like you are either using a version that does not provide transform or you lack permissions. ---
**Author:** ![Sugand](https://avatars.discourse-cdn.com/v4/letter/s/9d8465/32.png) [@Sugand](https://discuss.elastic.co/u/Sugand)\ **Post date:** [December 30, 2021, 5:36am UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/8 "2021-12-30T05:36:10Z")
@Hendrik_Muhs , I'm using version 7.10.2. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 27, 2022, 5:36am UTC](https://discuss.elastic.co/t/merge-2-index-based-off-fields-and-not-rows/292706/9 "2022-01-27T05:36:55Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.