# How to get the consolidated aggregartion on specfic part of String

**URL:** <https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432>\
**Category:** Elasticsearch\
**Created:** [October 8, 2020, 11:42am UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432 "2020-10-08T11:42:27Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![rajan01](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@rajan01](https://discuss.elastic.co/u/rajan01)\
**Post date:** [October 8, 2020, 11:42am UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432/1 "2020-10-08T11:42:27Z")

</div>

In the elastic search we have logs with fi\_id as BA-844, BAX-844, BA-SYSTEM-844 and BA-090, BAX-090, BA-SYSTEM-WER-090 and many more with same numeric value as last part of the string.

I want to write the elastic query to return the consolidated complete count, in which 844 and 090 is present as last part of the string after "-".

The below query gave me different counts as shown below: Sample:

```auto
GET /ibs-ccbilling-2020.09/_search
{
  "aggs": {
    "by_fiId": {
      "terms": {
        "field": "fi_id.keyword",
        "size": 100
      }
    }
  }
}

```

Result:

```auto
{
          "key": "D1BANK-334",
          "doc_count": 19
        },
        {
          "key": "BAX-777-System",
          "doc_count": 10
        },
        {
          "key": "BA-409",
          "doc_count": 6
        },
        {
          "key": "D1CONSUMER-334",
          "doc_count": 4
        },

```

I want the result would be 19+4 as 23 for key "334".

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [October 8, 2020, 12:03pm UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432/2 "2020-10-08T12:03:11Z")

</div>

You can use a [script](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-terms-aggregation.html#search-aggregations-bucket-terms-aggregation-script) in the terms.

However, it's highly recommended to improve the data as part of your ingest, meaning splitting the number and writing it into a separate field. You will achieve better runtime performance this way. The script is fine if you only want to do this once or rarely.

---

<div class="post-metadata">

**Author:** ![rajan01](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@rajan01](https://discuss.elastic.co/u/rajan01)\
**Post date:** [October 8, 2020, 12:13pm UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432/3 "2020-10-08T12:13:46Z")

</div>

> [@Hendrik\_Muhs](#):
>
> rove the data as part of your ingest, meaning splitting the number and writing it into a separate field. You will achieve better runtime pe

Hi,  
I tried to do that, but was stuck when I need to use the split function in aggregation.  
Can you please give some inputs there.  
I tried the below one, but dont know how exactly split the field and get the last element.  
"script": {  
"source": "doc['fi\_id.keyword'].getValue().substring(0,6)"  
and  
"script": {  
"source": "doc['fi\_id.keyword'].getValue().split('-').getlastelement()" // it didnt work

---

<div class="post-metadata">

**Author:** ![Hendrik\_Muhs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/hendrik_muhs/32/25802_2.png) [@Hendrik\_Muhs](https://discuss.elastic.co/u/Hendrik_Muhs)\
**Post date:** [October 8, 2020, 12:29pm UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432/4 "2020-10-08T12:29:47Z")

</div>

what about:

```auto
  "aggs": {
    "system_group": {
      "terms": {
        "script": {
          "source": """String system = doc['system.keyword'].value;
        return system.substring(system.lastIndexOf('-') + 1);
        """
        }
      }
    }
  }

```

---

<div class="post-metadata">

**Author:** ![rajan01](https://avatars.discourse-cdn.com/v4/letter/r/c89c15/32.png) [@rajan01](https://discuss.elastic.co/u/rajan01)\
**Post date:** [October 8, 2020, 12:44pm UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432/5 "2020-10-08T12:44:44Z")

</div>

Thanks :). It worked for me.

---

<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:** [November 5, 2020, 12:44pm UTC](https://discuss.elastic.co/t/how-to-get-the-consolidated-aggregartion-on-specfic-part-of-string/251432/6 "2020-11-05T12:44:50Z")

</div>

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