topic Re: [JSON; MongoDB; java mongodb driver] Data type issue (bigDecimal and date/datetime) in Talend Studio

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

JackStrong — Tue, 15 Mar 2022 13:11:06 GMT

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.

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 ?

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

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

gjeremy1617088143 — Tue, 15 Mar 2022 14:35:28 GMT

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

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

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

JackStrong — Tue, 15 Mar 2022 17:10:13 GMT

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.

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

gjeremy1617088143 — Tue, 15 Mar 2022 19:29:06 GMT

Wich version of Jackson library?

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

gjeremy1617088143 — Tue, 15 Mar 2022 19:36:54 GMT

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

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

JackStrong — Wed, 16 Mar 2022 11:34:11 GMT

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:

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

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

gjeremy1617088143 — Wed, 16 Mar 2022 12:38:10 GMT

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")