How to store money in elasticsearch

Question

I've created an elasticsearch index and my current mapping stores the dollar amount of an item as a string. This is proving to be difficult as I can't search/filter on this value correctly.

GET catalog/product/_search
{
  "filter": {
    "range": {
      "price": {
        "from": 230.0,
        "to": 300.0
      }
    }
  }
}

Where price is mapped as a string. I used a string because going from a python decimal value, I've had issues where this value suddenly gets values at something like 17.989999999999999999998789. This only happens sometimes but I don't wanna get into the issue of going from a python decimal to a java double/float (so I just str() the thing).

Any thoughts on a better approach? Should I bite the bullet and map the price to a double or a float?

Problem is that ElasticSearch has no `decimal` type so it is probably being converted to a `float`. Multiply by 100 and store as cents, then convert application-side? Bye bye precision issues. — Ant P, Jun 10 '15 at 21:00
That's a great idea. If you answer, I could mark it as such. The only issue is returning a lot of data, looping over all those items could be expensive (slow) application side. I could use a ES script but that's slow too I thought. — stincity, Jun 10 '15 at 21:17
You might also want to have a look at the first answer of this question http://stackoverflow.com/questions/1458633/how-to-deal-with-floating-point-number-precision-in-javascript — Val, Jun 11 '15 at 06:05

score 21 · Answer 1 · edited Jun 08 '17 at 21:33

21

In newer version (checked for 5.0), probably the best option is to use scaled_float with scaling_factor = 100 like in their example:

PUT my_index
{
  "mappings": {
    "my_type": {
      "properties": {
        "number_of_bytes": {
          "type": "integer"
        },
        "time_in_seconds": {
          "type": "float"
        },
        "price": {
          "type": "scaled_float",
          "scaling_factor": 100
        }
      }
    }
  }
}

You can find their doc here.

edited Jun 08 '17 at 21:33

Yosi Dahari

6,794
5
24
44

answered Nov 23 '16 at 14:50

The_Ham

427
3
7

1

how what is the syntax to add the data for a scaled_float data type. I am getting the error ` { "error": { "root_cause": [ { "type": "mapper_parsing_exception", "reason": "failed to parse" } ], "type": "mapper_parsing_exception", "reason": "failed to parse", "caused_by": { "type": "illegal_argument_exception", "reason": "Field [val] misses required parameter [scaling_factor]" } }, "status": 400 } ` – Bala Karthik Mar 26 '19 at 06:42
Had the same problem: the mapping correlates to a specific type (like "my_type") in the sample above. When using the High-Level-Rest-Client, it always creates the mappings under the new default "_doc" type. When inserting data using another type you get the error. After changing the insert to the _doc type the error was gone – lostiniceland Jun 11 '19 at 11:07
1

there are precision issues related to scaled_float type so it should not be used for finance fields/attributes https://github.com/elastic/elasticsearch/issues/32570 – dgt Jun 20 '19 at 14:34
1

@dgt it looks like the precision issues were fixed in 7.0 around Jan 2019 from that issue you linked to – jon_wu Aug 12 '20 at 00:31

score 12 · Accepted Answer · answered Jun 12 '15 at 21:52

This occurs because ElasticSearch has no built-in type for decimals or currency, so your value is likely being converted to a float and suffering from floating point precision issues.

You should be able to get around this by simply storing the value as a long (e.g. the number of cents rather than dollars) and converting to and from your decimal application-side.

Since you'll only ever do this conversion for values you are already enumerating, the performance impact should be negligible.

How to store money in elasticsearch

2 Answers2