# How to remove duplicate values in ealstic search **URL:** \ **Category:** Elasticsearch\ **Created:** [December 18, 2017, 12:16pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226 "2017-12-18T12:16:31Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 18, 2017, 12:16pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/1 "2017-12-18T12:16:31Z")
i am using elastic search 5.6.4 GET /clinicaldata/\_search { "query": { "match" : { "mrdno" : "112627" } } } in that mrdno 112627 have duplicate of 75 records. when run in the above shows ouput as follows { "took": 2, "timed\_out": false, "\_shards": { "total": 1, "successful": 1, "skipped": 0, "failed": 0 }, "hits": { "total": 75, "max\_score": 1 total 75 records. in this 75 duplicate records is there. i want to delete the duplicate records using elastic search for that how to write the query using elastic search ---
**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:** [December 18, 2017, 12:32pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/2 "2017-12-18T12:32:27Z")
You can probably use the delete by query feature and index again one of the docs ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 18, 2017, 3:23pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/3 "2017-12-18T15:23:00Z")
ok. please tell me. how to do. because i am new to elastic search ---
**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:** [December 18, 2017, 3:35pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/4 "2017-12-18T15:35:18Z")
See [https://www.elastic.co/guide/en/elasticsearch/reference/6.1/docs-delete-by-query.html](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/docs-delete-by-query.html) ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 18, 2017, 3:49pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/5 "2017-12-18T15:49:28Z")
i have duplicate 75 duplicate records. mrdno 11657 POST twitter/\_delete\_by\_query?scroll\_size=5000 { "query": { "term": { "user": "kimchy" } } } from the above example i am replacing my field name. my index name is test POST test/\_delete\_by\_query?scroll\_size=5000 { "query": { "term": { "mrdno": "11657" } } } The above query will delete the duplicate values for the column mrdno. the above one is correct please let me know ---
**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:** [December 18, 2017, 4:38pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/6 "2017-12-18T16:38:38Z")
Please format your code using `` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will make your post more readable. Or use markdown style like: ```` ``` CODE ``` ```` Your query looks good but the index name which should be probably `clinicaldata`. ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 18, 2017, 4:44pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/7 "2017-12-18T16:44:15Z")
clinical data is my index name POST clinicaldata/\_delete\_by\_query?scroll\_size=5000 { "query": { "term": { "mrdno": "11657" } } } The above query is correct ? to delete duplicate records of mrd no ---
**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:** [December 18, 2017, 5:45pm UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/8 "2017-12-18T17:45:11Z")
It will delete **ALL** records that match `"mrdno": "11657"` not only duplicates. So you will need to create again after a new document... ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 19, 2017, 4:14am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/9 "2017-12-19T04:14:48Z")
i do not want delete the all record that matches mrdno 11657. i want to delete only duplicates POST clinicaldata/\_delete\_by\_query?scroll\_size=5000 { "query": { "term": { "mrdno": "11657" } } } you told that above query will delete all the record that matches mrdno 11657. i want to delete duplicates records of mrdo 16657. for that what changes i have to make from the above query. please do the needful. ---
**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:** [December 19, 2017, 5:41am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/10 "2017-12-19T05:41:46Z")
I can see that you asked the same question at > [@How to avoid duplicate values in ealstic search 5.6.4](https://discuss.elastic.co/t/how-to-avoid-duplicate-values-in-ealstic-search-5-6-4/108976/2): > > want to delete the duplicates the below code is correct the below code is written in the logstash file under config file. file type is conf file. output { elasticsearch { hosts =\> ["localhost:9200"] manage\_template =\> false index =\> "test" } stdout { codec =\> rubydebug } document\_id =\> "%{[@metadata][\_mrdno]}" } want to remove the duplicates of mrdno column. please let me know So the answer is: there is no way to remove duplicates in one single call. As I said, you need to: - Remove all docs - Add back one of the docs you removed Is it a one time operation or something you want to do in the long run? If the later, what is the usecase of allowing duplicates ? ---
**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:** [December 19, 2017, 5:54am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/11 "2017-12-19T05:54:04Z")
Unless you have another field which can help like a timestamp... ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 19, 2017, 6:09am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/12 "2017-12-19T06:09:03Z")
in kibana discover tab data is displaying, below i show the discover tab details in kibana. clinicaldata (index name) Selected Fields ? \_source Available Fields ? @timestamp ? @version t \_id t \_index add \_score t \_type Fields name as follows ? age ? cpa\_addr\_1 ? cpa\_addr\_2 ? cpa\_addr\_3 ? cpa\_addr\_area ? cpa\_addr\_city ? cpa\_country\_cd ? cpa\_pin\_code ? cpa\_state\_cd ? mcs\_case\_summary ? mcs\_crt\_dt ? mcs\_crt\_uid ? rrh\_first\_name ? rrh\_location\_cd ? rrh\_mr\_num ? rrh\_pat\_dob ? rrh\_pat\_sex ? rrh\_regn\_dt \_source cpa\_addr\_2:POST- EKBARNA cpa\_addr\_area: - mcs\_crt\_uid:MACTCS cpa\_addr\_1:VILL- EKB ARNA rrh\_mr\_num:3416558 cpa\_pin\_code:732 204 rrh\_pat\_sex:Fema mcs\_crt\_dt:2015-01-03T07:23:00.000Z @timestamp:2017-12-15T08:54:28.231Z cpa\_country\_cd:INDIA cpa\_state\_cd:WB @version:1 rrh\_regn\_dt:2014-11-18T18:30:00.000Z rrh\_first\_name:ANITA KARMAKAR rrh\_pat\_dob:1987-08-24T18:30:00.000Z mcs\_case\_summary:External File Uploaded - EXTNFILEINFO SHEET age:28 rrh\_location\_cd:MAIN cpa\_addr\_3:PS- RATUA cpa\_addr\_city:india \_id:AWBZYcj9fbvNIqwo2O6C \_type:logs \_index:clinicaldata \_score:1 But when i do in kibana visualization using tag cloud i want to display the data in visualization Steps i follows to display data in visualization Create a new visualization select the tag cloud (in visualization type) select the index name Then select add a fliter + (Button) another popup is opened from the fliter list select the field name and selector operator is and in value textbox type the india and click save button then Message shows no result found what is the problem in visualization tab data not displaying. please do the needful. Steps i follows to display data in visualization i mentioned above. is there any mistake in above steps. ---
**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:** [December 19, 2017, 6:23am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/13 "2017-12-19T06:23:31Z")
So you can run a first request to get the min value of the timestamp field with a min aggregation. And then exclude this value in your search body. ---
**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:** [December 19, 2017, 6:51am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/14 "2017-12-19T06:51:23Z")
And please format your code using `` icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will make your post more readable. Or use markdown style like: ```` ``` CODE ``` ```` ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 19, 2017, 7:22am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/15 "2017-12-19T07:22:47Z")
ok. you told that first request to get the min value of the timestamp field with a min aggregation. And then exclude this value in your search body. how to do please tell me which each step. Because i am new to elastic search. please do the needful. ---
**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:** [December 19, 2017, 7:53am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/16 "2017-12-19T07:53:22Z")
- min aggregation: [Min Aggregation | Elasticsearch Reference [6.1] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/search-aggregations-metrics-min-aggregation.html) - exclude from resultset, see `must_not` clause from bool query: [Bool Query | Elasticsearch Reference [6.1] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/6.1/query-dsl-bool-query.html) If you still don't know, please provide a sample data set with some docs that we can use as described in > [@About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21): > > The heart of the free and open Elastic Stack Elasticsearch is a distributed, RESTful search and analytics engine capable of addressing a growing number of use cases. As the heart of the Elastic Stack, it centrally stores your data for lightning fast search, fine‑tuned relevancy, and powerful analytics that scale with ease. warning PLEASE READ THIS SECTION IF IT'S YOUR FIRST POST Some useful links: [elasticsearch reference guide](http://www.elastic.co/guide/en/elasticsearch/reference/current/index.html)[elasticsearch user guide](http://www.elastic.co/guide/en/elasticsearch/guide/current/index.html)[elasticsearch plugins](https://www.elastic.co/guide/en/elasticsearch/plugins/current/index.html)[elasticsearch cl…](https://www.elastic.co/guide/en/elasticsearch/client/index.html) It will help to better understand what you are doing. Please, try to keep the example as simple as possible. Please read carefully the instructions. It must be easily runnable. ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 19, 2017, 9:01am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/17 "2017-12-19T09:01:54Z")
removing the duplicates the below code is not working input { jdbc { jdbc\_driver\_library =\> "D:\mysql-connector-java-5.1.44\mysql-connector-java-5.1.44\mysql-connector-java-5.1.44-bin.jar" jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/sample" jdbc\_user =\> "root" jdbc\_password =\> "root" jdbc\_fetch\_size =\> 10000 ``` schedule => "* * * * *" statement => "SELECT * from sample" ``` #codec =\> "json" } } output { elasticsearch { hosts =\> ["localhost:9200"] manage\_template =\> false index =\> "clinical" } # document\_id =\> "%{[@metadata][\_RRH\_MR\_NUM]}" stdout { codec =\> rubydebug } } to remove the duplicates in elastic search i write the above code in my CONF file above. but above code is not working. Please do the needful. what is the mistake in my above code. ---
**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:** [December 19, 2017, 9:22am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/18 "2017-12-19T09:22:00Z")
Please format your code if you expect someone to read it. ---
**Author:** ![narasiman1986](https://avatars.discourse-cdn.com/v4/letter/n/ce73a5/32.png) [@narasiman1986](https://discuss.elastic.co/u/narasiman1986)\ **Post date:** [December 19, 2017, 9:44am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/19 "2017-12-19T09:44:45Z")
format my code and sent it again input { jdbc { jdbc\_driver\_library =\> "D:\mysql-connector-java-5.1.44\mysql-connector-java-5.1.44\mysql-connector-java-5.1.44-bin.jar" jdbc\_driver\_class =\> "com.mysql.jdbc.Driver" jdbc\_connection\_string =\> "jdbc:mysql://localhost:3306/sample" jdbc\_user =\> "root" jdbc\_password =\> "root" jdbc\_fetch\_size =\> 10000 ``` schedule => "* * * * *" statement => "SELECT * from sample" ``` #codec =\> "json" } } output { elasticsearch { hosts =\> ["localhost:9200"] manage\_template =\> false index =\> "clinical" } # document\_id =\> "%{[@metadata][\_RRH\_MR\_NUM]}" stdout { codec =\> rubydebug } } to remove the duplicates in elastic search i write the above code in my CONF file above. but above code is not working. please help me. what is wrong in my above code. i tried several times it is not working, ---
**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:** [December 19, 2017, 10:22am UTC](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/20 "2017-12-19T10:22:49Z")
Please read what I wrote here. > [@How to remove duplicate values in ealstic search](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226/14): > > And please format your code using \ icon as explained in [this guide](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will make your post more readable. Or use markdown style like: ``` CODE ``` Edit your post. Make sure in the preview window that it is correct. Thanks. [Next page](https://discuss.elastic.co/t/how-to-remove-duplicate-values-in-ealstic-search/112226.md?page=2)