# Find & Separately Store Array Duplicates

**URL:** <https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175>\
**Category:** Elasticsearch\
**Created:** [November 17, 2017, 5:23pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175 "2017-11-17T17:23:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![kbarresi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kbarresi/32/24394_2.png) [@kbarresi](https://discuss.elastic.co/u/kbarresi)\
**Post date:** [November 17, 2017, 5:23pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175/1 "2017-11-17T17:23:00Z")

</div>

Hello,

I have a large set of documents, each with an `item_ids` array of integers, like such:

```auto
{
   "item_ids" : [1, 2, 3, 3, 4 5, 5]
}

```

What I would like to do is filter a search by finding documents where the same item ID appears more than once. From what I understand, this is not possible because of the way ES indexes invert the array items (please correct me if this is not the case).

To get around this, my idea was to create a separate array called `primary_item_ids`, which would contain IDs that exist in `item_ids` more than once. In my above example, it would look like this when updated:

```auto
{
   "item_ids" : [1, 2, 3, 3, 4 5, 5],
   "primary_item_ids" : [3, 5]
}

```

I don't really care if the `primary_item_ids` has duplicates or not, since I'm just using it to filter searches.

My initial thought of how to do this was use `update_by_query` to have a Painless script:

1. Identify duplicate values in `item_ids`
2. Save those duplicates in the new `primary_item_ids` field.

I have no idea how to approach this in Painless. Any suggestions or ideas?

---

<div class="post-metadata">

**Author:** ![Rory](https://avatars.discourse-cdn.com/v4/letter/r/a183cd/32.png) [@Rory](https://discuss.elastic.co/u/Rory)\
**Post date:** [November 17, 2017, 6:57pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175/2 "2017-11-17T18:57:33Z")

</div>

I think the best way to do this is to store the documents with the count of items while indexing. You should look at nested documents: [https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html)

What your document should look like is:

```
{
  "item_id" : [
    {
      "item_value" : 1,
      "item_count" : 1
    },
    {
      "item_value" : 2,
      "item_count" : 1
    },
    {
      "item_value" : 3,
      "item_count" : 2
    },
    {
      "item_value" : 4,
      "item_count" : 1
    },
    {
      "item_value" : 5,
      "item_count" : 2
    }
  ]
}

```

When you want to query to find documents that have no duplicate item\_id, you just need a nested query with a range query inside that says item\_count not greater than 1

```
"query": {
    "bool": {
      "must_not": [
        {
          "nested": {
            "path": "item_id",
            "query": {
              "range": {
                "item_id.item_count": {
                  "gt": 1
                }
              }
            }
          }
        }
      ]
    }
  }

```

Let me know if that helps.

---

<div class="post-metadata">

**Author:** ![kbarresi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kbarresi/32/24394_2.png) [@kbarresi](https://discuss.elastic.co/u/kbarresi)\
**Post date:** [November 17, 2017, 7:06pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175/3 "2017-11-17T19:06:48Z")

</div>

That's definitely an option, but our dataset is quite large and re-indexing every document like this would take a very long time, and a lot of resources. I was hoping to use update\_by\_query so I could use the Task API.

---

<div class="post-metadata">

**Author:** ![kbarresi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kbarresi/32/24394_2.png) [@kbarresi](https://discuss.elastic.co/u/kbarresi)\
**Post date:** [November 22, 2017, 4:52pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175/4 "2017-11-22T16:52:18Z")

</div>

Any other suggestions on this?

---

<div class="post-metadata">

**Author:** ![kbarresi](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kbarresi/32/24394_2.png) [@kbarresi](https://discuss.elastic.co/u/kbarresi)\
**Post date:** [November 27, 2017, 5:16pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175/5 "2017-11-27T17:16:10Z")

</div>

I figured out a way to do this using an inline Painless script, coupled with the `_update_by_query` endpoint. Here's how I did it. Hopefully this will help others:

```auto
{
  "script": {
    "inline": "<script>...",  
    "lang": "painless",
    "params" : { }
  },
  "slice": {
    "id": 1,
    "max": 6
  }
}

```

And here is the script itself:

```auto
HashMap items = new HashMap();
ArrayList primaries = new ArrayList(); 
for (int i = 0; i < ctx._source.item_ids.length; i++) { 
    if (items.containsKey(ctx._source.item_ids[i])) { 
        primaries.add(ctx._source.item_ids[i]); 
    } 
    items.put(ctx._source.item_ids[i], true); 
} 
ctx._source.primary_item_ids = primaries;

```

---

<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:** [December 25, 2017, 5:16pm UTC](https://discuss.elastic.co/t/find-separately-store-array-duplicates/108175/6 "2017-12-25T17:16:18Z")

</div>

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