0

I'm having an index created in elasticsearch 5.0, where it contains data from my MySQL db. There's a field which is a string in my table, which I need it as a double in ES.

So what I did was added the mapping when I created the index for the appropriate field using a PUT:

{
  "mappings": {
    "my_type": {
      "properties": {      
        "chargeamount": {
          "type": "double"
        }
      }
    }
  }
}

After I did this, a value which contains numbers after the decimal (ie: 23.23) returns the value properly as a double but where as numbers which has zeros after the decimal (ie: 23.00) returns it as a string itself (ie: 2300).

EDIT: These are the steps which I did:

  1. I initially created the index through a PUT request (http://hostmachine:9402/indexname) with the above mapping as the body.

  2. Then I'm pushing the data (from my MySQL table) to the index using logstash. I could provide the logstash conf if needed.

  3. Once the data is being uploaded to the index, I tried querying as such in order to check whether the result shows a double value. The POST request (http://hostmachine:9402/indexname/_search? and the body as follows :

    {  
        "size" : 0,
       "query":{  
          "query_string":{  
             "query":"myquery"
          }
       },
       "aggs":{  
          "total":{  
             "terms":{  
                "field":"userid"
             },
             "aggs":{  
                "total":{  
                   "sum":{  
                       "script":{
                        "lang": "painless",
                        "inline" : "doc['chargeamount'].value"
                       }
                   }
                }
             }
          }
       }
    }

And the result looks like as in the snapshot below, where it should've been 267472.00:

enter image description here

Where am I going wrong? Any help could be appreciated.

Kulasangar
  • 9,046
  • 5
  • 51
  • 82
  • Can you show an example of the document you have indexed, and the commands you're running? – Val Nov 01 '16 at 06:16
  • @Val example of the document in the sense? I created the index using this (`http://hostmachine:9402/indexname` ) put request with the above mapping as the `body`. After I created the index as such I'm trying to upload data to the index using `logstash`. – Kulasangar Nov 01 '16 at 06:21
  • 2
    In simpler terms, can you show all the commands you ran so that one could recreate the same issue as you see? – Val Nov 01 '16 at 06:23
  • Ok now can you show two documents you've sourced from MySQL? One with `chargeamount` having decimals (e.g. 23.23) and another one without (e.g. 23.00). – Val Nov 01 '16 at 06:44
  • @Val you want me to show it from the db itself, or after querying in ES? – Kulasangar Nov 01 '16 at 06:52
  • I would like to see a document as it is indexed in ES, basically what you get from `curl -XGET localhost:9200/indexname/typename/id` – Val Nov 01 '16 at 06:53
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/127080/discussion-between-kulasangar-and-val). – Kulasangar Nov 01 '16 at 06:54
  • @Val I've posted the results after including "_source": ["chargeamount"], within the request body in the above discussion. – Kulasangar Nov 01 '16 at 09:15
  • 1
    I've answered back. – Val Nov 01 '16 at 13:32

1 Answers1

1

You need to make sure that the mapping type in your index creation query is exactly the same as the document_type you have in your logstash config, namely message_logs:

PUT response_summary6
{
  "mappings": {
    "message_logs": {          <--- change this
      "properties": {
        "userid": {
          "type": "text",
          "fielddata": true
        },
        "responsecode": {
          "type": "integer"
        },
        "chargeamount": {
          "type": "double"
        }
      }
    }
  }
}
Val
  • 207,596
  • 13
  • 358
  • 360