How to Count 3 columns together while doing group by in Elastic Transform in Kibana

Elastic Stack Elasticsearch
1

I have below data set in my index and I want to summarize my index via transform feature in kibana (yellow column shows count of available number for reference)

image
image813×141 3.01 KB

I am able to get individual columns count while doing group by, with below code in Transform.

{
  "group_by": {
    "customer_id": {
      "histogram": {
        "field": "customer_id",
        "interval": "10"
      }
    },  
  },
  "aggregations": {
    "phone1": {
      "value_count": {
        "field": "phone1"
      }
    },
    "phone2": {
      "value_count": {
        "field": "phone2"
      }
    },
    "phone3": {
      "value_count": {
        "field": "phone3"
      }
    }
  }
}

how can I count values of 3 different columns (phone1, phone2, phone3) together group by customer_id, so that count of all the numbers come together not seperately
A similar sql query would be like below -

SELECT count("phone1") + count("phone2") + count("phone3")  AS no_of_phone from table_name 
group by "customer_id"

Expected output -
image

2

Hi,

there is currently no easy way to do this. However you can use a scripted metric aggregation (bucket script might work, too). There is no exact example, but I hope you get inspired and are able to implement your usecase.

3

Hi @Hendrik_Muhs
Thanks for the reply, I thought it's a normal use case and it might be documented somewhere in elastic search website. However thank you for suggestions I'll look into scripted metric and bucket script.