Spring Data: How to Sort BigDecimal Fields Correctly When Using MongoDB

If you use spring data together with mongodb and one of your entity has some fields of type "BigDecimal", You have probably already noticed that such fields are actually saved as string in mongodb!

This caused a problem when you want to sort/order the entity on these fields. For example, if a "BigDecimal" field (say price) has the following values (in column "Unordered") for a few entities, you will retrieve an ordered list as shown in column "Ordered", while you actually expect the order as shown in column "Expected Order".

Unordered	Ordered	Expected Order
3.21 42.90 101.20 22.12	101.20 22.12 3.21 42.90	3.21 22.12 42.90 101.20

The good news is that you can fix the problem if you are using a MongoDB with version equal or greater than 3.4.

Since version 3.4, MongoDB has added a new data type "Decimal128". Since it is a number type, fields of this type is ordered by the number comparison instead of the string alphabet order. This data type is natural mapping candidate for "BigDecimal" in java. However, Spring Data still maps BigDecimal to string in MongoDB by default (I guess this is for backward compatibility).

So the fix is just to change the default mapping of "BigDecimal" from "String" to "Decimal128". If you use Spring Boot for your application, this can be easily done by injecting the following bean:

@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));
}

Here is a complete sample Spring Boot project on github that shows how to change the default mapping to make the ordering of "BigDecimal" field correct.

Please note, if you already have an application using the default mapping for BigDecimal, you may be careful when applying this fix. At least the following check list need to be followed:

• Make sure the MongoDB version is equal or greater than 3.4
• Convert the existing String fields (originally mapped as BigDecimal in Spring Data) of the documents to use Decimal128 (NumberDecimal) type in your MongoDB database.

If you just start a new project and want to use the new data type for better sorting result, you can just apply the custom mapping.