# Counting each key of an array inside aggregation

**URL:** <https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060>\
**Category:** Elasticsearch\
**Created:** [February 2, 2022, 11:36am UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060 "2022-02-02T11:36:39Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![hugomeloo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hugomeloo/32/101228_2.png) [@hugomeloo](https://discuss.elastic.co/u/hugomeloo)\
**Post date:** [February 2, 2022, 11:36am UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/1 "2022-02-02T11:36:39Z")

</div>

I am trying to make an aggregation in which I can count the number of times each key specific of an array is repeated. As an example:

```auto
curl -XPUT "localhost:9200/products/_mapping" -H 'Content-Type: application/json' -d'
{
  "properties": {
    "words": {
      "type": "keyword"
    }
  }
}'
curl -X POST "localhost:9200/products/doc/_bulk" -H 'Content-Type: application/json' -d'
{"index":{"_id":1}}
{"words":["room","kitchen","room"]}
{"index":{"_id":2}}
{"words":["room","restroom"]}
'

```

I wanted to have a count of  
room: 3  
kitchen: 1  
restroom: 1  
From what I got checking different similar questions, value\_count should help me on that, but what I get is:

```auto
curl -X POST "localhost:9200/products/_search?pretty" -H 'Content-Type: application/json' -d'
{
  "size": 0,
  "aggs": {
    "words": {
      "terms": {
        "field": "words"
      },
      "aggs": {
        "total": {
          "value_count": {
            "field": "words"
          }
        }
      }
    }
  }
}
'
{
  "aggregations" : {
    "words" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "room",
          "doc_count" : 2,
          "total" : {
            "value" : 4
          }
        },
        {
          "key" : "kitchen",
          "doc_count" : 1,
          "total" : {
            "value" : 2
          }
        },
        {
          "key" : "restroom",
          "doc_count" : 1,
          "total" : {
            "value" : 2
          }
        }
      ]
    }
  }
}

```

As you can see, it's counting the whole array size whenever the key is present. Is there a way to count only the times it appear in the array?

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 2, 2022, 12:48pm UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/2 "2022-02-02T12:48:12Z")

</div>

I realized value\_count aggregation works as the sum of unique values in documents in buckets.

One possility is using scripted metric aggregation.  
doc[field] are de-duplicated and I used params.\_source instead. This aggregation could be slow.

```auto
GET /test_products/_search
{
  "size":0,
  "aggs":{
    "value_count":{
      "scripted_metric": {
        "init_script": "state.map = new HashMap()",
        "map_script": "if (params._source[params.field] instanceof List) {for (val in params._source[params.field]){state.map[val] = state.map.getOrDefault(val,0)+1}} else {state.map[params._source[params.field].value] = state.map.getOrDefault(params._source[params.field].value,0)+1}",
        "combine_script": "return state.map",
        "reduce_script": "Map m = new HashMap();for (map in states){map.forEach((k,v)->m[k]=m.getOrDefault(k,0)+v)} return m",
        "params":{
          "field": "words"
        }
      }
    }
  }
}

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "value_count" : {
      "value" : {
        "kitchen" : 1,
        "room" : 3,
        "restroom" : 1
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![hugomeloo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hugomeloo/32/101228_2.png) [@hugomeloo](https://discuss.elastic.co/u/hugomeloo)\
**Post date:** [February 3, 2022, 12:56pm UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/3 "2022-02-03T12:56:29Z")

</div>

That works quite like I wanted, but it takes 15x more time to calculate. So, I am trying to find a solution reorganizing the field to be a hash of counts. Still strugging with the aggregation..  
Thanks!

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 3, 2022, 1:52pm UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/4 "2022-02-03T13:52:47Z")

</div>

Another way is using ingest pipeline to count terms in exchange for loads of indexing. Below is my sample script just for my practice.

And using this sterategy, you have to specify every words to sum counts in [sum aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-sum-aggregation.html#_script_10).

```auto
PUT _ingest/pipeline/test_word_count
{
    "processors": [
      {
        "script":{
          "source":"""
          Map map = new HashMap();
          if (ctx[params.field] instanceof List) {
            for (val in ctx[params.field]){
              map[val] = map.getOrDefault(val,0) + 1
            }
          } else {
            map[params.field] = 1
          }
          ctx[params.target] = map
          """,
          "params":{
            "field":"words",
            "target":"words_count"
          }
        }
      }
    ]
  }
  
PUT /test_products/_settings
{
  "index.default_pipeline": "test_word_count"
}

POST test_products/_update_by_query

GET test_products/_search

```

---

<div class="post-metadata">

**Author:** ![hugomeloo](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hugomeloo/32/101228_2.png) [@hugomeloo](https://discuss.elastic.co/u/hugomeloo)\
**Post date:** [February 3, 2022, 3:06pm UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/5 "2022-02-03T15:06:41Z")

</div>

The generation of this map is not a problem for me as I can change the structure of the field to whatever I want. The problem is the query with script which ran over a very big dataset can be too expensive.  
I could also create this field with information structured like:

```auto
{ "room": 2, "restroom": 1 }

```

or

```auto
{"topic": "room", "count": 2}, {"topic": "restroom", "count": 1 }

```

Thanks for helping!

---

<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:** [March 3, 2022, 3:07pm UTC](https://discuss.elastic.co/t/counting-each-key-of-an-array-inside-aggregation/296060/6 "2022-03-03T15:07:22Z")

</div>

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