# Finding all documents with duplicate properties **URL:** \ **Category:** Elasticsearch\ **Created:** [March 22, 2018, 11:00pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279 "2018-03-22T23:00:01Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![lauraerinmann](https://avatars.discourse-cdn.com/v4/letter/l/53a042/32.png) [@lauraerinmann](https://discuss.elastic.co/u/lauraerinmann)\ **Post date:** [March 22, 2018, 11:00pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/1 "2018-03-22T23:00:01Z")
I have to find every document in Elasticsearch that has duplicate properties. My mapping looks something like this: ``` "type": { "properties": { "thisProperty": { "properties" : { "id":{ "type": "keyword" }, "other_id":{ "type": "keyword" } } } ``` The documents I have to find have a pattern like this: ``` "thisProperty": [ { "other_id": "123", "id": "456" }, { "other_id": "123", "id": "456" }, { "other_id": "4545", "id": "789" }] ``` So, I need to find any document by type that has repeat property fields. Also I cannot search by term because I do not what the value of either Id field is. So far the API hasn't soon a clear way to do this via query. Is it possible? If so, how? ---
**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\ **Post date:** [March 23, 2018, 3:04pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/2 "2018-03-23T15:04:33Z")
The first thing you'd need to do is define `thisProperty` to be a `nested` type instead of a regular object. Without defining thisProperty as nested, the relationship between the `id` and `other_id` pairs is lost. [The docs go into more details on that topic](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html#_how_arrays_of_objects_are_flattened). The resulting mapping would look like this: ```auto { "mappings": { "type": { "properties": { "thisProperty": { "type": "nested", "properties": { "id": { "type": "keyword" }, "other_id": { "type": "keyword" } } } } } } } ``` Next, you could use a [nested aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-nested-aggregation.html) to aggregate on the nested objects. The goal here is to find duplicate objects, which is something you could achieve by running a [scripted terms aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script) that concatenates the document's `_id`, the value of `id` and of `other_id`. If we find any duplicates of the resulting concatenated field, we know that this document has a repeating set of properties. Adding `"min_doc_count": 2` to the terms aggregation will allow you to see just those duplicates. Putting all of this together would look like this: ```auto GET test/_search { "size": 0, "aggs": { "my_nested": { "nested": { "path": "thisProperty" }, "aggs": { "dupes": { "terms": { "script": """return doc['_id'].value + "_" + doc['thisProperty.id'].value + "_" + doc['thisProperty.other_id'].value;""", "size": 100, "min_doc_count": 2 } } } } } } ``` If your original document had an `_id` of 1, the resulting output of this aggregation would return: ```auto "buckets": [ { "key": "1_456_123", "doc_count": 2 } ] ``` ... telling you that document `1` has a duplicate set of properties where `id` is 456 and `other_id` is 123`. ---
**Author:** ![lauraerinmann](https://avatars.discourse-cdn.com/v4/letter/l/53a042/32.png) [@lauraerinmann](https://discuss.elastic.co/u/lauraerinmann)\ **Post date:** [March 23, 2018, 8:22pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/3 "2018-03-23T20:22:56Z")
Hi Abdon, Thank you for your thoughtful reply! One question, I do not need to preserve the relationship between id and other\_id (I removed the nested type for this reason). Can this same query be written without the nested type? ---
**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\ **Post date:** [March 26, 2018, 9:03am UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/4 "2018-03-26T09:03:12Z")
If you do not keep the relationship between the id and other\_id, what does it mean for properties to be duplicates? Are you just looking for multiple occurrences of id or of other\_id in a single document? Because without setting up a nested type, It's not possible to distinguish at search time between your document and this doc (notice none of the objects in this document are actual duplicates): ```auto { "thisProperty": [ { "other_id": "123", "id": "456" }, { "other_id": "123", "id": "789" }, { "other_id": "4545", "id": "456" }] } ``` or even this one: ```auto { "thisProperty.id": ["456", "456", "789"], "thisProperty.otherId": ["123", "123", "4545"] } ``` ---
**Author:** ![lauraerinmann](https://avatars.discourse-cdn.com/v4/letter/l/53a042/32.png) [@lauraerinmann](https://discuss.elastic.co/u/lauraerinmann)\ **Post date:** [March 26, 2018, 6:08pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/5 "2018-03-26T18:08:38Z")
Hi Abdon, We are looking for multiple occurrences of id and other\_id in a single document whose values are repeating (i.e id has the same same value more then once). I'm not sure I follow you on the nested explanation for this particular issue. ---
**Author:** ![abdon](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/abdon/32/9195_2.png) [@abdon](https://discuss.elastic.co/u/abdon)\ **Post date:** [March 26, 2018, 6:51pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/6 "2018-03-26T18:51:35Z")
Sorry about that. I was trying to make the point that I don’t think you can do what you’re trying to do without setting up the repeating property as a nested type. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 23, 2018, 6:51pm UTC](https://discuss.elastic.co/t/finding-all-documents-with-duplicate-properties/125279/7 "2018-04-23T18:51:53Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.