Manually group specified terms in Aggregations

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 ?

try this:

or this:

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

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
          }
        }
      ]
    }
  }


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 ?

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.

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

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