Manually group specified terms in Aggregations

Elastic Stack Elasticsearch
1

I want to manually specify terms to group in aggregations i have a query like this

{
    "index": "xxx-xxx",
    "size": 0,
    "body": {
            "query": {
                "bool": {
                    "must": [
                        {
                            "match": {
                                "referralId": "1234567890ertyui"
                            }
                        },
                        {
                            "match": {
                                "transactionType.keyword": "TRANSFER"
                            }
                        }
                    ]
                }
            },
            "sort": {
                "timeCreated": {
                    "order": "desc"
                }
            },
            "aggs": {
                "transfer_metrics": {
                    "terms": {
                        "field": "recipientBank.keyword"
                    },
                    "aggs": {
                        "value": {
                            "sum": {
                                "field": "amount"
                            }
                        },
                        "volume": {
                            "terms": {
                                "field": "transactionStatus.keyword"
                            }
                        }
                    }
                }
            }
    }
}

i get an aggregagtion like this, i am only posting the aggregation part.

"aggregations": {
            "transfer_metrics": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                    {
                        "key": "GTBank",
                        "doc_count": 39,
                        "volume": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "BILL PURCHASED FAILED",
                                    "doc_count": 29
                                },
                                {
                                    "key": "PAYMENT FAILED",
                                    "doc_count": 10
                                }
                            ]
                        },
                        "value": {
                            "value": 13815
                        }
                    },
                    {
                        "key": "GTB",
                        "doc_count": 29,
                        "volume": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "BILL PURCHASED FAILED",
                                    "doc_count": 15
                                },
                                {
                                    "key": "PAYMENT FAILED",
                                    "doc_count": 13
                                },
                                {
                                    "key": "PAYMENT SUCCESSFUL",
                                    "doc_count": 1
                                }
                            ]
                        },
                        "value": {
                            "value": 442097
                        }
                    },
                    {
                        "key": "Gtb",
                        "doc_count": 11,
                        "volume": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "BILL PURCHASED FAILED",
                                    "doc_count": 10
                                },
                                {
                                    "key": "PAYMENT FAILED",
                                    "doc_count": 1
                                }
                            ]
                        },
                        "value": {
                            "value": 6720
                        }
                    },
                    {
                        "key": "United Bank for Africa",
                        "doc_count": 2,
                        "volume": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "BILL PURCHASED FAILED",
                                    "doc_count": 2
                                }
                            ]
                        },
                        "value": {
                            "value": 100
                        }
                    },
                    {
                        "key": "Access Bank",
                        "doc_count": 1,
                        "volume": {
                            "doc_count_error_upper_bound": 0,
                            "sum_other_doc_count": 0,
                            "buckets": [
                                {
                                    "key": "BILL PURCHASED FAILED",
                                    "doc_count": 1
                                }
                            ]
                        },
                        "value": {
                            "value": 150
                        }
                    }
                ]
            }
        }

How do i tell Elasticsearch to group GTBank , GTB and Gtb together and probably return it under a key of a specified key of like GT Bank . And can I do it for multiple recipientBank in one query, e.g inclusive of the above i add... group FB , Fb and First Bank ?

2

try this:

or this:

3

But i will need to know all the recepientBank, when filtering.
Is it possible to tell Elasticsearch that when doing a terms aggregation, if it encounters something like Xx, XX,xx,xX , then it should aggregate all of them and group all of the records under XX? @casterQ

4

Hi.

Did you try to use some script?

Like this:

POST data/_doc/1
{
   "type": "regular",
   "size": 10
}

POST data/_doc/2
{
   "type": "Regular",
   "size": 10
}

POST data/_doc/3
{
   "type": "Medium",
   "size": 50
}

GET data/_search
{
  "size": 0,
  "aggs": {
    "bucket_name_regular": {
      "terms": {
        "script": {
          "source": "doc['type.keyword'].value == 'Regular' || doc['type.keyword'].value == 'regular' ? 'regular' : 'not_regular'"
        }
      },
      "aggs": {
        "sum": {
          "sum": {
            "field": "size"
          }
        }
      }
    }
  }
}

Result: 
  "aggregations" : {
    "bucket_name_regular" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "regular",
          "doc_count" : 2,
          "sum" : {
            "value" : 20.0
          }
        },
        {
          "key" : "not_regular",
          "doc_count" : 1,
          "sum" : {
            "value" : 50.0
          }
        }
      ]
    }
  }
5

Hi , thank you so much, I just tried it and it worked.
How will I add scripts for more keywords(recipientBanks)....as there are others I will like to group ?

6

Maybe you have to map the possibilities and add them to the script.
Another option is for your application to make this grouping from your rules.

I don't think it's possible to group automatically, the combinations must be known in advance.

7

What do you mean by map the possibilities and add them to the script?.
Sorry I'm quite new to Elasticsearch @RabBit_BR

8

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