How to get the consolidated aggregartion on specfic part of String

Elastic Stack Elasticsearch
1

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:

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

Result:

{
          "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".

2

You can use a 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.

3

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

4

what about:

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

Thanks :). It worked for me.

6

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