Sorting number values stored as a BigDecimal type

Question

I'm studying Spring Data MongoDB with a tutorial (https://spring.io/guides/tutorials/data/2/). In this tutorial, the type of cost is BigDecimal as following and it is stored as a "string" in MongoDB. So, when I tried to get a sort result with an ascending sort by the cost field, I got a wrong result.

I found that using the BigDecimal class is the best way for calculations. But, if I save numbers as a BigDecimal type with Spring Data MongoDB in MongoDB, it will be saved as a string type and I will get a wrong sort result like this tutorial.

What can I do to get a right sort result in terms of numbers for the sake of precision? Could you please help me to solve this problem? Thank you very much in advance.

For example,

1> MenuItem class

@Document(collection = "menu")
public class MenuItem {
  @Id
  private String id;

  @Field("itemName")
  @Indexed
  private String name;
  private BigDecimal cost;

2> create an instance of MenuItem

MenuItem item = new MenuItem();
item.setDescription("Peanutty Noodles, perfect for every occasion.");
item.setName("Yummy Noodles");
item.setCost(new BigDecimal("52.99"));


MenuItem item = new MenuItem();
item.setDescription("Rice, Egg Fried");
item.setName("Yummy Rice");
item.setCost(new BigDecimal("211.99")); 

3> sort result

db.menu.find().sort({cost:1})

{ "_id" : ObjectId("53e982f0300475a4fbab8c32"), "_class" : "com.yummynoodlebar.persistence.domain.MenuItem", "itemName" : "Yummy Rice", "description" : "Rice, Egg Fried", "ingredients" : [ { "name" : "Egg", "description" : "Chicken Eggs" }, { "name" : "Rice", "description" : "Straight White Rice" } ], "cost" : "211.99", "minutesToPrepare" : 0 }

{ "_id" : ObjectId("53e982f0300475a4fbab8c33"), "_class" : "com.yummynoodlebar.persistence.domain.MenuItem", "itemName" : "Yummy Rice", "description" : "Rice, Egg Fried", "ingredients" : [ { "name" : "Egg", "description" : "Chicken Eggs" }, { "name" : "Rice", "description" : "Straight White Rice" } ], "cost" : "211.99", "minutesToPrepare" : 0 }

{ "_id" : ObjectId("53e982f0300475a4fbab8c2f"), "_class" : "com.yummynoodlebar.persistence.domain.MenuItem", "itemName" : "Yummy Noodles", "description" : "Peanutty Noodles, perfect for every occasion.", "ingredients" : [ { "name" : "Peanuts", "description" : "A Nut" }, { "name" : "Egg", "description" : "Used in the noodles" }, { "name" : "Noodles", "description" : "Crisp, lovely noodles" } ], "cost" : "52.99", "minutesToPrepare" : 0 }

{ "_id" : ObjectId("53e982f0300475a4fbab8c30"), "_class" : "com.yummynoodlebar.persistence.domain.MenuItem", "itemName" : "Yummy Noodles", "description" : "Peanutty Noodles, perfect for every occasion.", "ingredients" : [ { "name" : "Peanuts", "description" : "A Nut" }, { "name" : "Egg", "description" : "Used in the noodles" }, { "name" : "Noodles", "description" : "Crisp, lovely noodles" } ], "cost" : "52.99", "minutesToPrepare" : 0 }

{ "_id" : ObjectId("53e982f0300475a4fbab8c31"), "_class" : "com.yummynoodlebar.persistence.domain.MenuItem", "itemName" : "Yummy Noodles", "description" : "Peanutty Noodles, perfect for every occasion.", "ingredients" : [ { "name" : "Egg", "description" : "Used in the noodles" }, { "name" : "Peanuts", "description" : "A Nut" }, { "name" : "Noodles", "description" : "Crisp, lovely noodles" } ], "cost" : "52.99", "minutesToPrepare" : 0 }

Answer 1

Unfortunately, BigDecimal is not supported by MongoDB natively. That's why we convert it into Strings by default.

For a more numeric handling, especially if you deal with prices or the like, we usually recommend to rather store doubles instead and convert them into BigDecimals for arithmetic operations in the domain object directly.

Answer 2

If you are dealing with Money -- and "cost" looks like that -- then use a Money class in your code. Money will carry amount and currency. Money has methods to fetch the amount as a BigDecimal, etc. Persist money in MongoDB as rich shape like this:

{ cost: { amt: 21199, ccode: "USD" }}

All same-currency numeric ops (eq, gt, lt, etc.) work precisely. And you cannnot do cross-currency comparison in the database anyway because the exchange rate is dynamic.

http://www.moschetti.org/rants/mongomoney.html

Answer 3

Since version 3.4, MongoDB has added the support for "BigDecimal" by the new decimal data type (decimal128). However, spring data still maps the Java BigDecimal to MongoDB string by default (I guess this is for backward compatibility). The good news is the default mapping can simply be overridden by injecting a CustomConversions in the spring boot application as follows:

@Bean
CustomConversions customConverions() {
  Converter<Decimal128, BigDecimal> decimal128ToBigDecimal = new Converter<Decimal128, BigDecimal>() {
    @Override
    public BigDecimal convert(Decimal128 s) {
      return s==null ? null : s.bigDecimalValue();
    }
  };

  Converter<BigDecimal, Decimal128> bigDecimalToDecimal128 = new Converter<BigDecimal, Decimal128>() {
    @Override
    public Decimal128 convert(BigDecimal s) {
      return s==null ? null : new Decimal128(s);
    }
  };

  return new CustomConversions(Arrays.asList(decimal128ToBigDecimal, bigDecimalToDecimal128));
}

Please check this post for more detailed information and even a complete example.