# Using aggregation in Elasticsearch to find duplicate data in the same index
**URL:** https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637
**Category:** Elasticsearch
**Tags:** painless
**Created:** [December 25, 2020, 1:54pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637 "2020-12-25T13:54:31Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Amraneze](https://discuss.elastic.co/u/Amraneze)
#### Post date: [December 25, 2020, 1:54pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/1 "2020-12-25T13:54:31Z")
Hi everyone,
I'm trying to find duplicates within the same index. I tried to use aggregation like this :
```auto
{
"aggs": {
"duplicates": {
"terms": {
"script": {
"source": "if (doc['username.sort-field'].size() == 0 || doc['firstName.sort-field'].size() == 0 || doc['lastName.sort-field'].size() == 0 || doc['email.sort-field'].size() == 0) return null; doc['username.sort-field'].value + doc['firstName.sort-field'].value + doc['lastName.sort-field'].value + doc['email.sort-field'].value",
"lang": "painless"
}
},
"aggs": {
"duplicateDocuments": {
"top_hits": {
"size": 1,
"_source": [
"username",
"firstName",
"lastName",
"email"
]
}
}
}
}
},
"size": 0
}
```
> Note: I'm giving an example with username and email, my model can have duplicates in all fields except the some ids.
And it works fine, I am getting this response from ES
```auto
{
"took": 3264,
"timed_out": false,
"_shards": {
"total": 2,
"successful": 2,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 10000,
"relation": "gte"
},
"max_score": null,
"hits": []
},
"aggregations": {
"duplicates": {
"doc_count_error_upper_bound": 179,
"sum_other_doc_count": 676366,
"buckets": [
{
"key": "key1...",
"doc_count": 371,
"duplicateDocuments": {
"hits": {
"total": {
"value": 371,
"relation": "eq"
},
"max_score": 1.0,
"hits": [
....
]
}
}
},
{
"key": "key2...",
"doc_count": 353,
"duplicateDocuments": {
"hits": {
"total": {
"value": 353,
"relation": "eq"
},
"max_score": 1.0,
"hits": [
....
]
}
}
}
...
]
}
}
}
```
I have two questions:
1. Is there any other way to find duplicates in an easy way ?
2. If the only way to find duplicates is with aggregation, is there a way to have a ES response like this ?
```auto
{
"took": 3264,
"timed_out": false,
"_shards": {
"total": 2,
"successful": 2,
"skipped": 0,
"failed": 0
},
"hits": {
"total": {
"value": 10000,
"relation": "gte"
},
"max_score": null,
"hits": []
},
"duplicates": [
{
"key": "key1...",
"doc_count": 371,
"duplicateDocuments": {
"hits": {
"total": {
"value": 371,
"relation": "eq"
},
"max_score": 1.0,
"hits": [
....
]
}
}
},
{
"key": "key2...",
"doc_count": 353,
"duplicateDocuments": {
"hits": {
"total": {
"value": 353,
"relation": "eq"
},
"max_score": 1.0,
"hits": [
....
]
}
}
}
...
]
}
```
Otherwise, I will need a lot of checking with Scala, just this code `result.aggregationsAsMap("duplicates").asInstanceOf[Map]("buckets")` to get to buckets list
Thank you so much and happy holidays 🙂
---
### Author:  [@ylasri](https://discuss.elastic.co/u/ylasri)
#### Post date: [December 25, 2020, 2:32pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/2 "2020-12-25T14:32:54Z")
Hi @Amraneze, Welcome to the community!
What is the objectif of findng duplicates ? just find duplicate or you have a plan de remove them ?
Here are somple examples on how to dedupliate data in elasticsearch
> **[How to Find and Remove Duplicate Documents in Elasticsearch](https://www.elastic.co/blog/how-to-find-and-remove-duplicate-documents-in-elasticsearch)**
>
> Learn how to detect and remove duplicate documents from Elasticsearch using Logstash or a custom Python script.
> **[Deduplicate data | Filebeat Reference \[7.10\] | Elastic](https://www.elastic.co/guide/en/beats/filebeat/current/filebeat-deduplication.html)**
---
### Author:  [@Amraneze](https://discuss.elastic.co/u/Amraneze)
#### Post date: [December 25, 2020, 2:46pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/3 "2020-12-25T14:46:21Z")
@ylasri To find them to display them by key. Actually, I gave just an example with username, firstName, lastName and email, because email and username should be unique.
---
### Author:  [@ylasri](https://discuss.elastic.co/u/ylasri)
#### Post date: [December 25, 2020, 4:00pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/4 "2020-12-25T16:00:27Z")
Try to use tranform
> **[Transforming data | Elasticsearch Reference \[7.10\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/transforms.html)**
---
### Author:  [@Amraneze](https://discuss.elastic.co/u/Amraneze)
#### Post date: [December 25, 2020, 4:21pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/5 "2020-12-25T16:21:06Z")
@ylasri that would be a huge impact on the performance of the application especially for transforming millions of data
> Transforms perform search aggregations on the source indices then index the results into the destination index. Therefore, a transform never takes less time or uses less resources than the aggregation and indexing processes.
> **[Transform limitations | Elasticsearch Guide \[master\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/master/transform-limitations.html)**
---
### Author:  [@ylasri](https://discuss.elastic.co/u/ylasri)
#### Post date: [December 25, 2020, 5:41pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/7 "2020-12-25T17:41:47Z")
Can you try this query, you will get only buckets with `doc_count > 1`
```auto
GET emails/_search
{
"size": 0,
"aggs": {
"duplicates": {
"composite": {
"sources": [
{ "email": { "terms": { "field": "email" } } },
{ "username": { "terms": { "field": "username" } } },
{ "first_name": { "terms": { "field": "first_name" } } },
{ "last_name": { "terms": { "field": "last_name" } } }
]
},
"aggs": {
"filter": {
"bucket_selector": {
"buckets_path": {
"doc_count": "_count"
},
"script": "params.doc_count > 1"
}
}
}
}
}
}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 22, 2021, 5:42pm UTC](https://discuss.elastic.co/t/using-aggregation-in-elasticsearch-to-find-duplicate-data-in-the-same-index/259637/8 "2021-01-22T17:42:12Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.