How can we aggregate SUM on column of type text in Elastic Search?

I have been trying to apply aggregate function for eg : sum on type of text my index mappings are below :

    ```
 {
    "my_elastic_search_index" : {
        "mappings" : {
        "properties" : {
            "@timestamp" : {
            "type" : "date"
            },
            "@version" : {
            "type" : "text",
            "fields" : {
                "keyword" : {
                "type" : "keyword",
                "ignore_above" : 256
                }
            }
            },
            "doc" : {
            "properties" : {
                "_id" : {
                "type" : "text",
                "fields" : {
                    "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                    }
                }
                },
                "last_updated_on" : {
                "type" : "long"
                },
                "sample_ids" : {
                "type" : "nested",
                "properties" : {
                    "name" : {
                    "type" : "text",
                    "fields" : {
                        "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                        }
                    }
                    },
                    "value" : {
                    "type" : "text",
                    "fields" : {
                        "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                        }
                    }
                    }
                }
                },
                "status" : {
                "type" : "text",
                "fields" : {
                    "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                    }
                }
                },
                "filter_id" : {
                "type" : "text",
                "fields" : {
                    "keyword" : {
                    "type" : "keyword",
                    "ignore_above" : 256
                    }
                }
                }
            }
            },
            "query" : {
            "properties" : {
                "match" : {
                "properties" : {
                    "doc" : {
                    "properties" : {
                        "filter_id" : {
                        "type" : "text",
                        "fields" : {
                            "keyword" : {
                            "type" : "keyword",
                            "ignore_above" : 256
                            }
                        }
                        }
                    }
                    }
                }
                }
            }
            }
        }
        }
    }
    }
My Data Is : 
    {
    "took" : 23,
    "timed_out" : false,
    "_shards" : {
        "total" : 1,
        "successful" : 1,
        "skipped" : 0,
        "failed" : 0
    },
    "hits" : {
        "total" : {
        "value" : 4,
        "relation" : "eq"
        },
        "max_score" : 1.0,
        "hits" : [
        {
            "_index" : "my_elastic_search_index",
            "_type" : "_doc",
            "_id" : "zzz-yyy-xxx-a9e8",
            "_score" : 1.0,
            "_source" : {
            "@version" : "1",
            "@timestamp" : "2019-11-14T14:30:56.261Z",
            "doc" : {
                "status" : "SENT",
                "sample_ids" : [
                {
                    "value" : """"20"""",
                    "name" : "8f4abde123d"
                },
                {
                    "value" : """"25.52"""",
                    "name" : "d92c4732bc9fb91"
                },
                {
                    "value" : """"0"""",
                    "name" : "4b91bdee68b6e"
                },
                {
                    "value" : """"xyz"""",
                    "name" : "bd0a944a292d5a"
                },
                {
                    "value" : """"someothervlue"""",
                    "name" : "8ee9932060d5bf"
                },
                {
                    "value" : """"30..01"""",
                    "name" : "229eed093fa0d85"
                },
                ],
                "filter_id" : "a1357913-cf99650f51d",
                "_id" : "zzz-yyy-xxx-a9e81",
            }
            }
        },
        {
            "_index" : "my_elastic_search_index",
            "_type" : "_doc",
            "_id" : "zzz-yyy-xxx-a9e82",
            "_score" : 1.0,
            "_source" : {
            "@version" : "1",
            "@timestamp" : "2019-11-14T14:30:56.731Z",
            "doc" : {
                "status" : "SENT",
                "sample_ids" : [
                {
                    "value" : """"40"""",
                    "name" : "8f4abde123d"
                },
                {
                    "value" : """"0"""",
                    "name" : "d92c47372bc9fb91"
                },
                {
                    "value" : """"0"""",
                    "name" : "4b91bdc6ee68b6e"
                },
                {
                    "value" : """"0"""",
                    "name" : "bccf07c19cfe12c"
                }
                ],
                "filter_id" : "a1357913-cf99650f51d",
                "_id" : "zzz-yyy-xxx-a9e84",
            },
            }
        },
        {
            "_index" : "my_elastic_search_index",
            "_type" : "_doc",
            "_id" : "zzz-yyy-xxx-a9e85",
            "_score" : 1.0,
            "_source" : {
            "@version" : "1",
            "@timestamp" : "2019-11-14T08:23:36.998Z",
            "doc" : {
                "status" : "SENT",
                "sample_ids" : [
                {
                    "value" : """"17.8"""",
                    "name" : "8f4abde123d"
                },
                {
                    "value" : """"35.6"""",
                    "name" : "d92c473132bc9fb91"
                },
                {
                    "value" : """"0"""",
                    "name" : "4b91bd5c6ee68b6e"
                },
                {
                    "value" : """"35.6"""",
                    "name" : "bd0a944c2a292d5a"
                },
                {
                    "value" : """"0"""",
                    "name" : "8ee9934dce9e2060d5bf"
                },
                {
                    "value" : """"0"""",
                    "name" : "229eed48xsscd3fa0d85"
                },
                {
                    "value" : """"30"""",
                    "name" : "4381f1bddffc4265129"
                },
                {
                    "value" : """"0"""",
                    "name" : "94cafdd1c78fc355b00"
                },
                {
                    "value" : """"HVDC"""",
                    "name" : "bccf024ac19cfe12c"
                }
                ],
                "filter_id" : "a1357913-cf99650f51d",
                "_id" : "zzz-yyy-xxx-a9e84",
                }
            }
        },
        {
            "_index" : "my_elastic_search_index",
            "_type" : "_doc",
            "_id" : "zzz-yyy-xxx-a9e87",
            "_score" : 1.0,
            "_source" : {
            "@version" : "1",
            "@timestamp" : "2019-11-14T08:24:01.272Z",
            "doc" : {
                "sample_ids" : [
                {
                    "value" : """"11.08"""",
                    "name" : "8f4abde123d"
                },
                {
                    "value" : """"35.6"""",
                    "name" : "d92c4737a132bc9fb91"
                },
                {
                    "value" : """"0"""",
                    "name" : "4b91bd5028c6ee68b6e"
                },
                {
                    "value" : """"35.6"""",
                    "name" : "bd0a9445e19c2a292d5a"
                },
                {
                    "value" : """"0"""",
                    "name" : "8ee9934dd002060d5bf"
                },
                {
                    "value" : """"0"""",
                    "name" : "229eed48e2093fa0d85"
                }
                ],
                "filter_id" : "a1357913-cf99650f51d",
                }
            }
        }
        ]
    }
    }

I want to return the result of sum of doc.sample_ids.value as 88.88 where doc.sample_ids.name = 8f4abde123d and filter_id = a1357913-cf99650f51d , already tried to cast doc.sample_ids.name into Number and that is giving me error. Is there is anyway I can get the sum , avg and count.

GET /my_elastic_search_index/_search
{
    "query": {
    "bool": {
    "must": [
        {
        "terms": {
            "doc.filter_id.keyword": [
            "a1357913-cf99650f51d"
            ]
        }
        }
    ]
    }
},
    "aggs": { 
    "sum_values" : { "sum" : { "script" : { 
    "lang":"painless", "inline" : "Double.parseDouble(doc['sample_ids.value'])" } } } }
    }

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