# Query to return documents where the list field in each document contains duplicate item values

**URL:** <https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277>\
**Category:** Elasticsearch\
**Created:** [March 3, 2017, 7:15am UTC](https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277 "2017-03-03T07:15:52Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![agf](https://avatars.discourse-cdn.com/v4/letter/a/9fc348/32.png) [@agf](https://discuss.elastic.co/u/agf)\
**Post date:** [March 3, 2017, 7:15am UTC](https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277/1 "2017-03-03T07:15:52Z")

</div>

I am wondering how to construct a query that can return documents where each contains a list field, but with duplicate values in that field. Here's an example setup:

```
PUT nestedtest
{
  "mappings": {
    "type": {
      "properties": {
        "history": {
          "type": "nested"
        }
      }
    }
  }
}

PUT nestedtest/type/1
{
    "name" : "Bob",
    "history" : [ 
    { 
        "date" : "2016-01-01", 
        "status" : "None" , 
        "color" : "red" 
    }, 
    { 
        "date" : "2016-01-01",
        "status" : "None" , 
        "color" : "blue" 
    },
    { 
        "date" : "2016-01-02", 
        "status" : "None", 
        "color" : "green" 
    } 
    ]
}

PUT nestedtest/type/2
{
    "name" : "Jane",
    "history" : [ 
    { 
        "date" : "2016-01-01", 
        "status" : "None",
         "color" : "red" 
    }, 
    {
         "date" : "2016-01-01",
         "status" : "Done", 
        "color" : "blue" 
    },
    { 
        "date" : "2016-01-02", 
        "status" : "None", 
        "color" : "green"
    } 
    ]
}

```

My interest is in querying the "history" field, which has been mapped as a nested field, in each document and checking for cases where the "date" field matches and where the "status"=="None" field match. So in this case, the query will return the first document with "\_id" : 1, because the first and second item of it's "history" field contains the same value for "date" and "status"=="None" respectively.

---

<div class="post-metadata">

**Author:** ![ywelsch](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ywelsch/32/7751_2.png) [@ywelsch](https://discuss.elastic.co/u/ywelsch)\
**Post date:** [March 3, 2017, 11:51am UTC](https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277/2 "2017-03-03T11:51:26Z")

</div>

I think that it is not possible to do this with your current mapping, see also the discussion here: [https://github.com/elastic/elasticsearch/issues/16380](https://github.com/elastic/elasticsearch/issues/16380)  
Similar to the suggestion on the ticket, you could duplicate the \_id into the nested docs and then use a multi-fields term aggregation (scripted terms agg concatenating date, status, and the id of the parent) with `min_doc_count = 2`.

---

<div class="post-metadata">

**Author:** ![agf](https://avatars.discourse-cdn.com/v4/letter/a/9fc348/32.png) [@agf](https://discuss.elastic.co/u/agf)\
**Post date:** [March 6, 2017, 9:52am UTC](https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277/3 "2017-03-06T09:52:30Z")

</div>

Hi @ywelsch, thanks for the link. I'm looking to avoid duplicating the \_id field into the list as I don't really have much control over the mapping of fields in the documents since we stream data from our production MongoDB database into Elasticsearch; the example I have shown here is a much simplified version of the problem I'm dealing with in my production data. I figure this query could be implemented via the use of scripted metric aggregations and Groovy scripts. Some ideas or suggestions along these lines would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![agf](https://avatars.discourse-cdn.com/v4/letter/a/9fc348/32.png) [@agf](https://discuss.elastic.co/u/agf)\
**Post date:** [March 8, 2017, 7:08am UTC](https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277/4 "2017-03-08T07:08:24Z")

</div>

Here's a Groovy scripted query that exactly solved my problem! It's a bit slow but does the job:

```
POST nestedtest/_search
{
  "query": {
    "script": {
      "script": {
        "inline": "_source.history.findAll {it.status == 'None'}.countBy {it.date}.find {it.value > 1} != null"
      }
    }
  }
}
```

---

<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:** [April 5, 2017, 7:08am UTC](https://discuss.elastic.co/t/query-to-return-documents-where-the-list-field-in-each-document-contains-duplicate-item-values/77277/5 "2017-04-05T07:08:45Z")

</div>

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