# Firing aggregation on field having String as its data type

**URL:** <https://discuss.elastic.co/t/firing-aggregation-on-field-having-string-as-its-data-type/51543>\
**Category:** Elasticsearch\
**Created:** [June 1, 2016, 10:09am UTC](https://discuss.elastic.co/t/firing-aggregation-on-field-having-string-as-its-data-type/51543 "2016-06-01T10:09:44Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Abhay](https://avatars.discourse-cdn.com/v4/letter/a/b77776/32.png) [@Abhay](https://discuss.elastic.co/u/Abhay)\
**Post date:** [June 1, 2016, 10:09am UTC](https://discuss.elastic.co/t/firing-aggregation-on-field-having-string-as-its-data-type/51543/1 "2016-06-01T10:09:44Z")

</div>

I have a use case where I have to fire aggregation on a field having string as its data type. But that field contains numeric as well as alpha-numeric values.

```
{
            "_index": "website",
            "_type": "blog1",
            "_id": "2",
            "_score": 1,
            "_source": {
               "title": "Title",
               "text": "Just trying this out...",
               **"linenum": "20"**
            }
         },
         {
            "_index": "website",
            "_type": "blog1",
            "_id": "4",
            "_score": 1,
            "_source": {
               "title": "Title",
               "text": "Just trying this out...",
               **"linenum": "10892AC"**
            }
         },
         {
            "_index": "website",
            "_type": "blog1",
            "_id": "1",
            "_score": 1,
            "_source": {
               "title": "Title",
               "text": "Just trying this out...",
               **"linenum": "10"**
            }
         }

```

Is there any way where I can get sum of linenum field ignoring the alphanumeric value.  
I have tried using script in the query as well.

```
{
    "query": {
        "match_all": {}
    },
    "fields": "*",
     "aggs" : {
        "linenum_Sum" : { 
            "sum" : { 
                "script" : "Float.parseFloat(doc['linenum'].value)" }
        }
    }
    
}

```

But even this query will give me only 10 in the result including script exception.

```
{
            "shard": 2,
            "index": "website",
            "node": "pHlqkKB5QMi8vy4TUY-Cog",
            "reason": {
               "type": "script_exception",
               "reason": "failed to run inline script [Float.parseFloat(doc['linenum'].value)] using lang [groovy]",
               "caused_by": {
                  "type": "number_format_exception",
                  "reason": "For input string: \"ac\""
               }
            }
         }
```

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [June 1, 2016, 10:59am UTC](https://discuss.elastic.co/t/firing-aggregation-on-field-having-string-as-its-data-type/51543/2 "2016-06-01T10:59:10Z")

</div>

Your script needs to deal with the exception itself. If a script does not catch a thrown exception the aggregation will fail. You'll need to do something like the following code (disclaimer: this snippet is completely untested and I do not know a lot of groovy, I wrote this after doing a quick google search for groovy exception handling)

Also note that you should be careful with allowing inline groovy scripts to run on your cluster (see [https://www.elastic.co/blog/scripting-security](https://www.elastic.co/blog/scripting-security) and [https://www.elastic.co/guide/en/elasticsearch/reference/2.3/modules-scripting.html](https://www.elastic.co/guide/en/elasticsearch/reference/2.3/modules-scripting.html))

```auto
{
    "query": {
        "match_all": {}
    },
    "fields": "*",
     "aggs" : {
        "linenum_Sum" : { 
            "sum" : { 
                "script" : "try { return Float.parseFloat(doc['linenum'].value); } catch (NumberFormatException e) { return 0; }" }
        }
    }
    
}

```

The script should catch the number format exception and return `0` as the value for this document. Note that it does not look to see if the number format exception was thrown on an alphanumeric string or a malformed number string so if it encounters badly formatted numbers it will not fail the aggregation.

---

<div class="post-metadata">

**Author:** ![Abhay](https://avatars.discourse-cdn.com/v4/letter/a/b77776/32.png) [@Abhay](https://discuss.elastic.co/u/Abhay)\
**Post date:** [June 1, 2016, 12:48pm UTC](https://discuss.elastic.co/t/firing-aggregation-on-field-having-string-as-its-data-type/51543/3 "2016-06-01T12:48:01Z")

</div>

It worked. Many Thanx

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:47pm UTC](https://discuss.elastic.co/t/firing-aggregation-on-field-having-string-as-its-data-type/51543/4 "2017-07-05T22:47:16Z")

</div>