I have a simple job to import data from SQL Server to MongoDB and i noticed something strange when inserting data into Mongo.
Some fields that are empty in SQL server are being filled with data in Mongo when it should be null.
Example:
If i execute this query:
select SUBSTRING(UF204,1,14) as imei_14, uf204 as imei, uf102 as trafego, uf201 as Origem, uf211 as Destino, uf301 as Data, uf300 as hora, uf400 as Duração, uf202 as IMSI, uf203 as LAC_CI
from dbo.History20210605 where uf101=1 and ProcID like 'MCR_09_PRA_CSR4_%' and uf301 = 20210605 and uf201='5885732'"
The result are 4 lines and it's comming with null fields and being inserted as it should in Mongo.
But if i change the query to (removed last part - and uf201='5885732'😞
select SUBSTRING(UF204,1,14) as imei_14, uf204 as imei, uf102 as trafego, uf201 as Origem, uf211 as Destino, uf301 as Data, uf300 as hora, uf400 as Duração, uf202 as IMSI, uf203 as LAC_CI
from dbo.History"+context.table_date +" where uf101=1 and ProcID like 'MCR_09_PRA_CSR4_%' and uf301 = 20210605"
The result of this query are 2 million lines that are inserted into Mongo. If i search for the {uf201: 5885732}
the results are the same 4 lines from first query but the fields that should be null are not, which is causing data to be incorrect.
I have a java code(image) that converts the string from SQL to number before inserting to Mongo but don't think that causes any issue.
Any idea on what can be the cause of this? Let me know if you need any more info.
Edit:
The problem seems to be in the conversion to Long. I made that conversion so the search would be faster since the fields are mostly numbers but they are commng as strings from SQL.
If i put them as strings in mongo it seems to work fine but that would slow dow the search. Any suggestions?
Could you please clarify in which Talend version/edition you are?
Is the schema column number type correctly mapped to mongodb collection field type with tMongoDBOutput component? What's Mongodb version are you using? Mongodb 3.6?
