[JSON; MongoDB; java mongodb driver] Data type issue (bigDecimal and date/datetime)

[JackStrong]

Hi All.

I'm loading the JSON files to MongoDB using mongo-java-driver-3.12.7.jar library .

Almost everything is ok but there is an issue regarding data types.

1

Currently when I have big decimal value in the file (let say 1234567890123456789.1234567), after loading to Mongo the data type is not Decimal128 (means big decimal) but it is Double. It causes that the value in MongoDB looks like this 1234567890123456800.

Probably the issue is because I'm using Document class (import org.bson.Document).

How to force MongoDB to write big decimal value as Decimal128?

I tried to change Document to BsonDocument (import org.bson.BsonDocument) but I got stuck because I'm not able to call getCollection method without errors.

For Document there is no error message when I call (but there is data type issue):

  MongoCollection<Document> collection = database.getCollection(context.collection_name);

For BsonDocument I'm trying to call getCollection but without success (method overloading):

  MongoCollection<BsonDocument> collection = database.getCollection(context.collection_name, java.lang.Class<TDocument> documentClass);

  What should I put instead of java.lang.Class<TDocument> documentClass ?

2

Similar (but different) case for date/datetime values.

Is there any way to force MongoDB to write and store date/datetime values as Date data type?

I'm asking because whenever I load the data to Mongo date and datetime values are stored as strings (there is no date/datetime type in JSON).

To be honest I found the way how to do that in case of org.bson.Document class:

  ...

  DateFormat formatterDateTime = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSXXX");

  ...

  Date dateOrderDateTime = formatterDateTime.parse(doc.getString("orderDateTime"));

  doc.append("orderDateTime", dateOrderDateTime);

but this approach requires to know what are the field names (keys). I have one big generic job which is responsible for processing more less fifty different json files (each file has a different schema) and I need to store the date/datetime values as Date in MongoDB for all of the date/datetime fields/keys regardless of the name.

Best regards,

JackStrong

[gjeremy1617088143]

Hi for decimal maybe you have to something like this in your json :

{ "totalValue" : NumberDecimal("100.0005") }

[JackStrong]

Hmmm.

Thanks for that. Do you know how to write in JSON file (I'm using jackson library) NumberDecimal(something) ?

I can write only boolean (true, false), strings (eclosed by double quotes), numbers, JSON objects and arrays and null.

[gjeremy1617088143]

Wich version of Jackson library?

[gjeremy1617088143]

below 2.9.10 you could as this to your Mapper :

mapper.configure(Feature.WRITE_BIGDECIMAL_AS_PLAIN, true);

else

mapper.enable(JsonGenerator.Feature.WRITE_BIGDECIMAL_AS_PLAIN);

[JackStrong]

Hi @guenneguez jeremy​ .

I see you have a big knowledge regarding java and JSON. Thank you for all your replies.

Unfortunately, till now I'm not able to achieve what I want.

In my case the whole scenario looks like this:

1. Pick up the JSON file (array of documents) and split into multiple files (one file one document). Split files are located in Talend job server. (JSON deserialization and serialization)
2. Then pick up split files one by one and load the documents to MongoDB. (JSON deserialization)

Once step 1. is completed I should have correct JSON files in Talens server.

As I know { "totalValue" : NumberDecimal("100.0005") } is not correct JSON (because "NumberDecimal(" and ")" strings).

And to be honest right know I am able to create correct JSON split files in Talend server even if they contain big decimal values. Probably this is because source JSON file contains big decimals as strings so it is enough to read them as strings and then put new BigDecimal("100.0005") constructor. It works. I have correct JSON split files. Step 1 completed.

But loading the JSON files to MongoDB (step 2) is challenging.

Using java mongodb driver and:

a) MongoCollection<Document>collection = database.getCollection(collection_name);

It doesn't work because big decimal values from JSON file are treated as Double.

b) MongoCollection<BsonDocument>collection = database.getCollection(collection_name, java.lang.Class<TDocument> documentClass);

It throws exception because I don't know what should be put instead of text in bold.

To be honest I'm not sure if this approach will work. I was not able to run the job even once because exception.

[gjeremy1617088143 · Accepted solution]

NumberDecimal

New in version 3.4.

The mongo

 shell treats all numbers as 64-bit floating-point 

double

 values by default. The mongo

 shell provides the

NumberDecimal()

 constructor to explicitly specify 128-bit decimal-based floating-point values capable of emulating decimal rounding with exact precision. This functionality is intended for applications that handle monetary data, such as financial, tax, and scientific computations.

The 

decimal

 BSON type uses the IEEE 754 decimal128 floating-point numbering format which supports 34 decimal digits (i.e. significant digits) and an exponent range of −6143 to +6144.

The 

NumberDecimal()

 constructor accepts the 

decimal

 value as a string:

NumberDecimal("1000.55")