# Exclude array items in cardinality aggregation

**URL:** <https://discuss.elastic.co/t/exclude-array-items-in-cardinality-aggregation/232331>\
**Category:** Elasticsearch\
**Created:** [May 12, 2020, 11:09pm UTC](https://discuss.elastic.co/t/exclude-array-items-in-cardinality-aggregation/232331 "2020-05-12T23:09:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ninan\_Zhang](https://avatars.discourse-cdn.com/v4/letter/n/47e85d/32.png) [@Ninan\_Zhang](https://discuss.elastic.co/u/Ninan_Zhang)\
**Post date:** [May 12, 2020, 11:09pm UTC](https://discuss.elastic.co/t/exclude-array-items-in-cardinality-aggregation/232331/1 "2020-05-12T23:09:38Z")

</div>

The field mapping is like this:

```auto
     [
         {
           "type": 1,
           "document_id": [4, 5]
         },
         {
           "type": 1,
            "document_id": [4]
         },
         {
           "type": 2,
           "document_id": [5]
         },
         {
           "type": 2,
           "document_id": [4,5]
         }
       ]  

```

Now I am trying to get the unique document id count of type 1 and type 2, the tricky part is, I don't want to count the document ids again in type 2 if they had been counted in type 1.

For example, by using the cardinality aggregation

```auto
 {
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "type": 1
          }
        }
      ]
    }
  },
  "aggs": {
    "document_count": {
      "cardinality": {
        "field": "document_id"
      }
    }
  }
}

```

I can get there are 2 unique document ids in type 1. If I do the same query for type 2, I will get count 2 as well.  
But the expected result I am trying to get is counting 2 for type 1, counting 0 for type 2 because I'd like to exclude document id 4 and 5 from type 2 since they have been counted in type 1.

Does anyone know if this is doable please?  
Thanks!

---

<div class="post-metadata">

**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\
**Post date:** [May 15, 2020, 12:59am UTC](https://discuss.elastic.co/t/exclude-array-items-in-cardinality-aggregation/232331/2 "2020-05-15T00:59:07Z")

</div>

I've tried to solve the issue with `scripted_metric` aggregation.

# Demo data

```auto
PUT testd/_doc/1
{
  "type": 1,
  "document_id": [
    4,
    5
  ]
}
PUT testd/_doc/2
{
  "type": 1,
  "document_id": [
    4
  ]
}
PUT testd/_doc/3
{
  "type": 2,
  "document_id": [
    5
  ]
}
PUT testd/_doc/4
{
  "type": 2,
  "document_id": [
    4,
    5
  ]
}

```

# Aggregation

This can only work on Elasticsearch 7.7.

```auto
GET testd/_search
{
  "aggs": {
    "NAME": {
      "scripted_metric": {
        "init_script": "state.types = new HashMap();",
        "map_script": "def t = doc['type'].value.toString(); if (!state.types.containsKey(t)) { state.types[t] = new HashSet(); }\nstate.types[t].addAll(doc['document_id']);",
        "combine_script": "return state;",
        "reduce_script": "def type1 = new HashSet(); def type2 = new HashSet(); for (s in states) { type1.addAll(s.types['1']); type2.addAll(s.types['2']); } type2.removeAll(type1); return ['1': type1.size(), '2': type2.size()]"
      }
    }
  }
}

```

Alternative for pre 7.7 (slightly less efficient).

```auto
GET testd/_search
{
  "aggs": {
    "NAME": {
      "scripted_metric": {
        "init_script": "state.types = new HashMap();",
        "map_script": "def t = doc['type'].value.toString(); if (!state.types.containsKey(t)) { state.types[t] = new HashMap(); }\n for(d in doc['document_id']) { state.types[t][d] = true; }",
        "combine_script": "return state;",
        "reduce_script": "def type1 = new HashSet(); def type2 = new HashSet(); for (s in states) { type1.addAll(s.types['1'].keySet()); type2.addAll(s.types['2'].keySet()); } type2.removeAll(type1); return ['1': type1.size(), '2': type2.size()]"
      }
    }
  }
}

```

Result:

```auto
...
  "aggregations" : {
    "NAME" : {
      "value" : {
        "1" : 2,
        "2" : 0
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Ninan\_Zhang](https://avatars.discourse-cdn.com/v4/letter/n/47e85d/32.png) [@Ninan\_Zhang](https://discuss.elastic.co/u/Ninan_Zhang)\
**Post date:** [May 15, 2020, 6:57pm UTC](https://discuss.elastic.co/t/exclude-array-items-in-cardinality-aggregation/232331/3 "2020-05-15T18:57:26Z")

</div>

Thanks a lot @Luca_Belluccini! That is exactly what I need.

---

<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:** [June 12, 2020, 6:57pm UTC](https://discuss.elastic.co/t/exclude-array-items-in-cardinality-aggregation/232331/4 "2020-06-12T18:57:28Z")

</div>

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