What datatype is to be used for the values (+00000.30) and (-00000.32) while loading with mongoimport with columnsHaveTypes and fields options?

Need to know what kind of datatype I need to use for the values (+00000.30) and (-00000.32) while loading data into MongoDB using mongoimport with columnsHaveTypes and fields

Hello @ganga_prasad, welcome to the MongoDB Community forum!

From mongoimport --columnsHaveTypes the following are possible types for your use case:

type        Supported Arguments	    Example Header Field
decimal()   None                    price.decimal()
double()	None                    revenue.double() 

The default data type for numeric data in MongoDB is double. Depending upon your use case you can consider using NumberDecimal. This data type has capability for decimal rounding with exact precision. See Data Types in mongo Shell for notes on NumberDecimal.


Thank you Prasad for your feedback. I have tested loading data with both data types decimal and double and the data after loading looks like ā€˜0.30ā€™. Iā€™m not able to load source data ā€˜+00000.30ā€™.

If you are looking for data to be formatted exactly like that - then that would be of string type. Then, that would be of not much use if you are using it in calculations. It is good for display purposes. You can use string type and convert to numeric type during calculations.

What is the reason you want the data to be in that format? It is unusual for numbers to be like that - it can only be a string.

MongoDB provides aggregation operators (see Type Expression Operators) to convert from one data type to another. During your operations you can apply these operators to convert from one format to another as per your need. Finally, it all depends upon your use case, the application, the queries, etc.

As per data functionality + sign indicates credit and - sign indicates debit amount, if there is no specific dedicated data type which can hold both sign and decimal value in MongoDB.

I think it is a design problem - how you model the data. Ideally, since it is a currency field the data type should be NumberDecimal with a negative (-) sign for negative values. The default is positive values. So, all positives are credits and negatives are debits.

Another way to approach this is to store everything as positive, and introduce a new field which indicates the amount is a credit or debit.

See this topic: Model Monetary Data.

Thank you prasad ! Let me get back once I get reply from customer.basically we are migrating dada from IBM mainframe source data to MongoDB.mainframe data will be in flat files.

As per the above update customer has changed the data set and removed the sign for decimal data now data we have is one field (9.2) and other field has (13,2) eg : 000000000.30 when iā€™m using NumberDecimal() data loading is failed due invalid datatype ā€¦i tried using double datatype and decimal getting same error.

I see you have various fields with different formats to import into the MongoDB database.

The available data types in MongoDB can accommodate all types of fields. The remaining aspect is your application and processes. It is common to have data formats of wide range during data migration from legacy systems. It is the process and the models that should take care of these scenarios.

I have already provided you some ideas about data types and possibilities. I hope you will be able to figure further (I am afraid it goes beyond the scope of this topic to discuss every field and datatype you are encountering :slightly_smiling_face:).