# Compare Two Indexes **URL:** \ **Category:** Kibana\ **Created:** [April 5, 2021, 5:50pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254 "2021-04-05T17:50:15Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 5, 2021, 5:50pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/1 "2021-04-05T17:50:15Z")
Hi, I have one document in one index and other document in other index. Now I want to compare the fields of these two documents of different indexes. Is it possible to do it, not sure did some couldn't find any satisfactory answer. ---
**Author:** ![mattkime](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattkime/32/43522_2.png) [@mattkime](https://discuss.elastic.co/u/mattkime)\ **Post date:** [April 5, 2021, 7:31pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/2 "2021-04-05T19:31:53Z")
Hello @Dinesh_Sharma Could you provide a sample of the schema for the two indices and explain how you plan to match one to the other? Thanks, Matt ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 6, 2021, 5:54am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/3 "2021-04-06T05:54:36Z")
Hi @mattkime , I have two csv which contains around 10k row each.Now I want to ingest them in two different indexes. Suppose name of the indexes are A and B. In index A, every document contains a field IP1 and in index B, every document contains a field IP2. My aim is to perform IP1==IP2. How can I do it? CSV1 name,age,IP1 CSV2 name,age,IP2 Here IP1: IP address IP2: IP Address Note: I tried to ingest these two CSV in single index and perform IP1==IP2 but since there is no way in this case to do the same. So I am thinking to do this with two different indexes. ---
**Author:** ![mattkime](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mattkime/32/43522_2.png) [@mattkime](https://discuss.elastic.co/u/mattkime)\ **Post date:** [April 6, 2021, 2:09pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/4 "2021-04-06T14:09:10Z")
The simplest way is to simply get everything into a single index. Could you merge the CSVs before ingesting them? Past that, you might ingest once CSV and then iterate through the data in the second CSV, updating the index. ---
**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:** [April 6, 2021, 2:17pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/5 "2021-04-06T14:17:57Z")
We have a [transform example](https://www.elastic.co/guide/en/elasticsearch/reference/current/transform-painless-examples.html#painless-compare) for something like this. If you only have 10k rows however, you don't need a transform, but you can do it in a single search request. You can use the `scripted_metric` aggregation from the example in the search request instead of using transform. I hope that gives you an idea. ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 7, 2021, 7:39am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/6 "2021-04-07T07:39:43Z")
Hi, I tried the transform way on two test indexes. Code is attached. ```auto { "id" : "index_compare", "source" : { "index" : [ "test1_index", "test2_index" ], "query" : { "match_all" : { } } }, "dest" : { "index" : "compare" }, "pivot" : { "group_by" : { "unique-id" : { "terms" : { "field" : "" } } }, "aggregations" : { "compare" : { "scripted_metric" : { "map_script" : "state.doc = new HashMap(params[\u0027_source\u0027])", "combine_script" : "return state", "reduce_script" : " \n if (states.size() != 2) {\nreturn \"count_mismatch\"\n }\n if (states.get(0).equals(states.get(1))) {\nreturn \"match\"\n } else {\nreturn \"mismatch\"\n }" } } } } } ``` I am not able to understand the **unique identifier** and do I need to make any other change in the above code. As I want to get the all in document where the IP Field of both the document of the indexes are same. The content of ingested two csv files are show below: new1.csv (ingested in test1.index) ![image](https://us1.discourse-cdn.com/elastic/original/3X/f/e/fe1244f8408a4d95aed29dea4e446d71c6d742de.png) new2.csv(ingested in test2.index) ![image](https://us1.discourse-cdn.com/elastic/original/3X/a/9/a95d82da97b811a7f26ed50766b8c4014c8da06e.png) ---
**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:** [April 7, 2021, 8:18am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/7 "2021-04-07T08:18:01Z")
The `group_by` defines which field(s) to use for grouping it together: > [@Dinesh\_Sharma](#): > > I want to get the all in document where the IP Field of both the document of the indexes are same There you go, if you specify the field name of the ip field, the pivot groups docs with the same IP together. You can rename the output field, e.g: ```auto "group_by" : { "ip" : { "terms" : { "field" : "ip" } } ``` Whatever you specify for `field` must match the field name that you used when indexing your docs. ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 7, 2021, 8:53am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/8 "2021-04-07T08:53:02Z")
Hi @Hendrik_Muhs, Great! , it is working like charm:) I have two doubts please assists with those too: (1) If I ingest these two csv in single index then also is there any way to comapre the **IP** column as we are doing in two indexes? (2) The transform code that I wrote above is there any way to visualize it or to see it in discover tab? ---
**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:** [April 7, 2021, 9:04am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/9 "2021-04-07T09:04:12Z")
> [@Dinesh\_Sharma](#): > > (1) If I ingest these two csv in single index then also is there any way to comapre the **IP** column as we are doing in two indexes? Yes, that's possible. For the transform it makes no difference if the data originates from 1 or 2 or more indexes. > [@Dinesh\_Sharma](#): > > (2) The transform code that I wrote above is there any way to visualize it or to see it in discover tab? If you want to visualize it, you need to create the transform. The example I gave used `POST _transform/_preview`, that's only the preview endpoint. A real transform is a task that you can create using the transform API's. I suggest to familiarize yourself starting from [here](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html). With continuous transform you can let transform update the destination index as new data comes in. A real transform just writes the data into a new index, therefore you can use it just as any other index and e.g. run visualization/discover/... on it. If you use the transform API's directly that requires 1 additional step: the creation of a kibana index pattern. But there is also a transform UI, the UI can create the pattern for you. ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 7, 2021, 9:52am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/10 "2021-04-07T09:52:47Z")
Created successfully. Thanks:) ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 7, 2021, 11:17am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/11 "2021-04-07T11:17:18Z")
Hi @Hendrik_Muhs , I used the below code to preview the transform. It is giving wrong result. IP "10.11.1.2" is present in both indexes but it is giving result as mismatch. PFA screenshot attached. ```auto POST /_transform/_preview?pretty { "id": "index_compare", "source": { "index": [ "test1_index", "test2_index" ], "query": { "match_all": {} } }, "dest": { "index": "compare" }, "pivot": { "group_by": { "unique-id": { "terms": { "field": "IP.keyword" } } }, "aggregations": { "compare": { "scripted_metric": { "map_script": "state.doc = new HashMap(params['_source'])", "combine_script": "return state", "reduce_script": """ if (states.size() != 2) { return "count_mismatch" } if (states.get(0).equals(states.get(1))) { return "match" } else { return "mismatch" }""" } } } } } ``` ![image](https://us1.discourse-cdn.com/elastic/original/3X/b/3/b3d7c1f6e3f14f43e1044d5d5872675ada7343c9.png) ---
**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:** [April 7, 2021, 11:44am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/12 "2021-04-07T11:44:40Z")
The scripted metric is just an example, it compares to indices and aims check if 2 indexes are equal. What are you looking for? What should happen if IP1==IP2? ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 7, 2021, 11:50am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/13 "2021-04-07T11:50:56Z")
Hi @Hendrik_Muhs , If IP1==IP2 then it should simply return "match" but it is returning mismatch. Do I need to modify this script? please suggest any edit in script. ---
**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:** [April 7, 2021, 12:01pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/14 "2021-04-07T12:01:47Z")
The group by already ensures that the IP's match. The idea behind the example script is to ensure that the same doc exists in 2 indexes, for that every group of documents must have a count of 2, that's what: ` if (states.size() != 2)` does. Next it compares if the full documents are the same, it sounds like you don't want that deep compare, therefore you can simplify the reduce script: ```auto "reduce_script": """ if (states.size() != 2) { return "count_mismatch" } return "match" """ ``` ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 7, 2021, 4:02pm UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/15 "2021-04-07T16:02:04Z")
Hi @Hendrik_Muhs , The above suggested code is working like a charm. Thanks for the same. (1) If I want to apply the transform on single index then will the below code work for me: ```auto POST /_transform/_preview?pretty { "id": "index_compare", "source": { "index": [ **"compare_pim_index"** ], "query": { "match_all": {} } }, "dest": { "index": "compare" }, "pivot": { "group_by": { "unique-id": { "terms": { "field": "IP.keyword" } } }, "aggregations": { "compare": { "scripted_metric": { "map_script": "state.doc = new HashMap(params['_source'])", "combine_script": "return state", "reduce_script": """ if (states.size() != 2) { return "count_mismatch" } return "match" """ } } } } } ``` (2) And If instead of comparing the same field name document , can I compare two document with the two different field name like some document in a index will have IP1.keyword and some will have IP2.keyword then will the below code work: ```auto POST /_transform/_preview?pretty { "id": "index_compare", "source": { "index": [ "compare_pim_index" ], "query": { "match_all": {} } }, "dest": { "index": "compare" }, "pivot": { "group_by": { "unique-id": { "terms": { **"field": "IP1.keyword",** **"field1":"IP2.keyword"** } } }, "aggregations": { "compare": { "scripted_metric": { "map_script": "state.doc = new HashMap(params['_source'])", "combine_script": "return state", "reduce_script": """ if (states.size() != 2) { return "count_mismatch" } return "match" """ } } } } } ``` ---
**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:** [April 8, 2021, 7:07am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/16 "2021-04-08T07:07:26Z")
grouping by `terms` can only take 1 field, I think you have to stay with the 2-index approach. Is there a reason not to? ---
**Author:** ![Dinesh\_Sharma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dinesh_sharma/32/86511_2.png) [@Dinesh\_Sharma](https://discuss.elastic.co/u/Dinesh_Sharma)\ **Post date:** [April 8, 2021, 7:31am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/17 "2021-04-08T07:31:12Z")
Hi @Hendrik_Muhs, Two index approach is working quite fine. I was just trying to do in one index so that we don't need to change our existing setup. But since this test case is quite important to us so two index approach is also fine. Thanks buddy! for your help during this long conversation:) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [May 6, 2021, 7:31am UTC](https://discuss.elastic.co/t/compare-two-indexes/269254/18 "2021-05-06T07:31:41Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.