I am doing the etl for loading data from sql server to mongodb. I have a decimal type field in sql but when i do the loading process,data type for this field is changed to int32 in mongodb. Does anybody have any idea that why this is happening? thanks in advance.
Asked
Active
Viewed 275 times
0
-
Mongo doesn't have a decimal datatype. Depending on your adapter, it might convert it to string, int, or double/float. – RizJa Feb 01 '17 at 17:30
-
it is converting the value to int in mongodb. if i want to show the values as double/float, do i need to convert decimal to double/float first ? – deepanshu nagpal Feb 01 '17 at 17:34
-
Yes, when you're loading data into Mongo, the type should be double/float instead of decimal. How exactly are you loading data into Mongo? Script task? Or some third party component? – RizJa Feb 01 '17 at 21:28
-
m using the derived column for transforming decimal into float, then loading into mongodb but still it is taking int32. – deepanshu nagpal Feb 02 '17 at 14:31
-
SSIS doesn't natively support connecting to MongoDB from what I recall. You must be using a third party utility. There could be an issue with that utility in how the datatype mapping is done. Also, check your records in Mongo to see if all your floating point numbers are being converted to int. For example, 1.0 might get pushed in as 1 (type int) but 1.1 might get pushed in as 1.1 (type double). Can you verify this? – RizJa Feb 02 '17 at 16:38
-
yes, I am using zappysis as a third party tool and this is what happening in mongodb that 1.0 is pushed as 1 and 1.1 is pushing as double. How can I resolve this ? – deepanshu nagpal Feb 03 '17 at 18:20
1 Answers
0
Answering based on what I specified in the comments:
MongoDB doesn't have a decimal type supported as of today. You'll have to make do with int or float/double.
When you push any number with a .0 such as 1.0 into Mongo, it records it as an int but any floating point number like 1.1 will be treated as a double.
This should not be a problem because Mongo can run aggregations just fine without type casting. Remember, Mongo specifies types at the document field level rather than at the column level in SQL. I trust most drivers will implicitly cast the value if you're mapping that field to a double type for a strongly typed object.
If you really needed to do this, look at this post, where I had to update the field type from a string to a double. The same logic will apply to a int to double conversion as well.
