Converting a string value into a double in Elasticsearch 5.0.0


(Kulasangar Gowrisangar) #1

I've done this in the earlier versions of elasticsearch by simply converting the identical field (chargeamount) within the aggs tag which looks like this:

{  
   "query":{  
      "query_string":{  
         "query":"api:\"payment\" AND transactionoperationstatus:\"charged\"AND year:2016 AND month:09 AND day:01 AND userid:\"codapayments*\" AND operatorid:\"XL\" AND responsecode:(200 201)"
      }
   },
   "aggs":{  
      "total":{  
         "terms":{  
            "field":"userid"
         },
         "aggs":{  
            "total":{  
               "sum":{  
                   "script":{"inline" : "Double.parseDouble(doc['chargeamount'].value)"}
               }
            }
         }
      }
   }
}

But then I'm struggling to do it in the elasticsearch version 5.0.0 where initially I had to do the mapping for the index as following:

{
  "mappings": {
    "type": {
      "properties": {
        "chargeamount": {
          "type": "text",
          "fielddata": true
        }
      }
    }
  }
}

Then I tried to send an http POST with the following body:

{  
   "query":{  
      "query_string":{  
         "query":"api:\"payment\" AND transactionoperationstatus:\"charged\"AND year:2016 AND month:09 AND day:01 AND userid:\"codapayments*\" AND operatorid:\"XL\" AND responsecode:(200 201)"
      }
   },
   "aggs":{  
      "total":{  
         "terms":{  
            "field":"userid"
         },
         "aggs":{  
            "total":{  
               "sum":{  
                   "script":{"inline" : "Double.parseDouble(doc['chargeamount'].value)"}
               }
            }
         }
      }
   }
}

But the result still shows as a string. Where am I going wrong? :thinking:

Any help could be appreciated.


(Nik Everett) #2

You might want "type": "keyword" here so we don't try an analyze the text. You may also want "type": "double" so you get numbers.

Can you post the result and point out the thing that is wrong? Could you try "inline": "return Double.parseDouble(doc['chargeamount'].value)"?


(Kulasangar Gowrisangar) #3

@nik9000 Thanks for the reply. I'm having the mapping as :

{
  "mappings": {
    "my_type": {
      "properties": {
        "userid": {
          "type": "text",
          "fielddata": true
        },
        "responsecode": {
          "type": "integer"
        },
        "chargeamount": {
          "type": "double",
          "coerce": true
        }
      }
    }
  }
}  

And then i'm trying to query as such:

{  
   "query":{  
      "query_string":{  
         "query":"api:\"payment\" AND transactionoperationstatus:\"charged\"AND year:2016 AND month:09 AND day:01 AND userid:\"codapayments*\" AND operatorid:\"XL\" AND responsecode:(200 201)"
      }
   },
   "aggs":{  
      "total":{  
         "terms":{  
            "field":"userid"
         },
         "aggs":{  
            "total":{  
               "sum":{  
                   "script":{
                   	"inline": "return Double.parseDouble(doc['chargeamount'].value)"
                   }
               }
            }
         }
      }
   }
}

And it throws a script exception.


(Nik Everett) #4

Now that you've set the mapping to double you don't need the script at all. The script error is, in a rather strange way, telling you that "You can't call Double.parseDouble on double. It needs a String".


(Kulasangar Gowrisangar) #5

@nik9000 So If i'm to get the sum of chargeamount as in the above query what should I be having within the query tag?

Something like this:

 "aggs":{  
            "total":{  
               "sum":{  
                   "script":{
                   	"inline" : "doc['chargeamount'].value"
                   }
               }
            }
         }

With the above I'm getting the sum as a string but not as a double. I mean the sum value isn't having any decimals. :confused:


(Nik Everett) #6

It'd work but I think:

"sum": {
   "field": "chargeamount"
}

will be a bit lower overhead.

Ooh! Another thing. In 5.0 the default scripting language is painless, an embedded oriented language that we made for Elasticsearch. It is new so I expect it to be a bit rough around the edges. It is the default because it is properly secured, unlike groovy, so we can enable it by default.


(system) #7