Search code examples
mongodbsortingbigdecimalspring-data-mongodb

Sorting number values stored as a BigDecimal type with Spring Data MongoDB in MongoDB

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 }

Solution

  • 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.