# Effective Way to Remove Existing Duplicate Documents in ElasticSearch

**URL:** <https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798>\
**Category:** Elasticsearch\
**Created:** [December 16, 2020, 3:30am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798 "2020-12-16T03:30:41Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![test\_tester](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/test_tester/32/79990_2.png) [@test\_tester](https://discuss.elastic.co/u/test_tester)\
**Post date:** [December 16, 2020, 3:30am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/1 "2020-12-16T03:30:41Z")

</div>

Hi Everyone,

Using aggregation, I am able query out doc\_count: 272152 of duplicates instances in my elasticsearch database.

The problem now is if I were to simply run a \_delete\_by\_query, it will delete everything including the original.

What effective strategy can I use to retain my original file?

Reading online, I've read that one possible solution is to 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.

Please advise.

```auto
{
  "size": 0,
  "aggs": {
    "duplicateDocs": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "createdDate": {
                  "from": "2020-06-01",
                  "to": null,
                  "include_lower": true,
                  "include_upper": true,
                  "boost": 1
                }
              }
            },
            {
              "bool": {
                "should": [
                  {
                    "terms": {
                      "messageType": [
                        "short",
                        "long",
                        "superlong"
                      ]
                    }
                  },
                  {
                    "prefix": {
                      "messagetype": "veryshort"
                    }
                  }
                ],
                "must_not": [
                  {
                    "terms": {
                      "matchingType": [
                        "six",
                        "one"
                      ]
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      "aggs": {
        "duplicateCount": {
          "terms": {
            "field": "Txn_Ref.keyword",
            "min_doc_count": 2,
            "size": 1000
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [December 16, 2020, 3:59am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/2 "2020-12-16T03:59:15Z")

</div>

you can extract all ids.  
and then use delete API to delete all those documents.  
It is 5 minutes of work.  
The syntax is something like

```auto
POST my_index/_delete_by_query
{
    "query" : {
        "terms" : {
            "_id" : 
              [ "id1",
              "id2",
              "id9",
              "id832" ]
        }
    }
}

```

---

<div class="post-metadata">

**Author:** ![test\_tester](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/test_tester/32/79990_2.png) [@test\_tester](https://discuss.elastic.co/u/test_tester)\
**Post date:** [December 16, 2020, 4:04am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/3 "2020-12-16T04:04:41Z")

</div>

Hi AClerk,

Thank you for the idea -\> however as mentioned there are at least 200k instances. So that means I would have to manually eyeball and go through 200k IDs for your method to work (it took me 2 hours to sieve through 100 txns).

Is there a way to quickly extract all 'wrong' ids?

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [December 16, 2020, 4:07am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/4 "2020-12-16T04:07:34Z")

</div>

Extract them all and filter in a spreadsheet (like excel).  
Then build your query with the filtered IDs.

I dont know how your data looks like, so it is hard to say how exactly you can filter and extract.

---

<div class="post-metadata">

**Author:** ![test\_tester](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/test_tester/32/79990_2.png) [@test\_tester](https://discuss.elastic.co/u/test_tester)\
**Post date:** [December 16, 2020, 4:13am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/5 "2020-12-16T04:13:07Z")

</div>

When you say extract them all to spreadsheet, how do I do that?

From what i know, elastic search plugin \> structured query can download CSV files. But does not exactly allow for aggregation query itself to be executed.

If you don't mind could you advise from my existing query, how I can effectively extract the data to an excel spread sheet?

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [December 16, 2020, 4:35am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/6 "2020-12-16T04:35:26Z")

</div>

You can use SQL.  
Works well in 7.9.1 I have

```auto
POST /_sql?format=csv
{
  "query": "SELECT filed_1, filed_2, filed_3, filed_N FROM \"my-index\" where ... order by ..."
}

```

Then just copy&paste into a spreadsheet and process the data there.

> **[SQL access | Elasticsearch Reference \[7.10\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html)**

---

<div class="post-metadata">

**Author:** ![test\_tester](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/test_tester/32/79990_2.png) [@test\_tester](https://discuss.elastic.co/u/test_tester)\
**Post date:** [December 16, 2020, 4:38am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/7 "2020-12-16T04:38:58Z")

</div>

Unfortunately, we are currently using Version 7.4.2.

Are there any other alternatives or is it mandatory to do an version upgrade?

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [December 16, 2020, 4:41am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/8 "2020-12-16T04:41:06Z")

</div>

> [@test\_tester](#):
>
> Unfortunately, we are currently using Version 7.4.2.

And? it is not supported?

---

<div class="post-metadata">

**Author:** ![test\_tester](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/test_tester/32/79990_2.png) [@test\_tester](https://discuss.elastic.co/u/test_tester)\
**Post date:** [December 16, 2020, 6:06am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/9 "2020-12-16T06:06:41Z")

</div>

I just tried, and it wasn't working, something about

- "type": "illegal\_argument\_exception",
- "reason": "Rejecting mapping update to [testindex] as the final mapping would have more than 1 type: [\_doc, \_sql]"

What I did was I replaced my url from  
testindex/\_sql?format=csv

to

testindex/\_doc?format=csv

However nothing happens after that, it says successful and this was the output:  
{

- "\_index": "testindex",
- "\_type": "\_doc",
- "\_id": "QsYwanYBg7zkLFbasLND",
- "\_version": 1,
- "result": "created",
- "\_shards": {
  - "total": 2,
  - "successful": 1,
  - "failed": 0},

- "\_seq\_no": 630563,
- "\_primary\_term": 16  
}

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [December 16, 2020, 10:57pm UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/10 "2020-12-16T22:57:26Z")

</div>

Can you provide the full script?

---

<div class="post-metadata">

**Author:** ![test\_tester](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/test_tester/32/79990_2.png) [@test\_tester](https://discuss.elastic.co/u/test_tester)\
**Post date:** [December 17, 2020, 3:35am UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/11 "2020-12-17T03:35:59Z")

</div>

I did this:

```auto
POST
testindex/_sql?format=csv
{"query":"SELECT * FROM testindex WHERE created_date < '2020-12-16'"}

```

And it threw this error

```auto
* "type": "illegal_argument_exception",
* "reason": "Rejecting mapping update to [testindex] as the final mapping would have more than 1 type: [_doc, _sql]"

```

Then i changed the script to:

```auto
POST
testindex/_ **doc**?format=csv
{"query":"SELECT * FROM testindex WHERE created_date < '2020-12-16

```

And finally got this:

```auto
* "_index": "testindex",
* "_type": "_doc",
* "_id": "QsYwanYBg7zkLFbasLND",
* "_version": 1,
* "result": "created",
* "_shards": {
  * "total": 2,
  * "successful": 1,
  * "failed": 0},
* "_seq_no": 630563,
* "_primary_term": 16
}

```

---

<div class="post-metadata">

**Author:** ![AClerk](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/aclerk/32/55297_2.png) [@AClerk](https://discuss.elastic.co/u/AClerk)\
**Post date:** [December 17, 2020, 11:31pm UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/12 "2020-12-17T23:31:15Z")

</div>

Instead of `select *`, try `select field_1`  
If it works, add the other relevant fields.

Also, need not specify the index. You will say which one it is in the from clause

```auto
POST
**testindex** /_sql?format=csv
{
    ....
}

```

--\>

```auto
POST /_sql?format=csv
{
  "query": "SELECT filed_1, filed_2, filed_3, filed_N FROM \"my-index\" where ... order by ..."
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 14, 2021, 11:31pm UTC](https://discuss.elastic.co/t/effective-way-to-remove-existing-duplicate-documents-in-elasticsearch/258798/13 "2021-01-14T23:31:27Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.