I am generating the JSON structure to insert to my MongoDB using tMongoDBOutput stage. In the Configure JSON Tree, I have specified attribute "type" as integer for the Integer fields.But the job is failing when NULL values are incoming from the source. My source is a SQL server DB.
Can anyone assist on this please.
This is a number format exception. which means " "(one space as value) cannot insert this value in integer as it is a string.
Seems you are directly converting a string to Integer. Treat both fields as string then add a tMap after this and apply the below function.
in Tmap target flow define both these fiedls as Integers.
row1.EMPID != null ? new Integer(row1.EMPID.trim()) : Integer.valueOf("")
repeat the same for Dept id in expression editor.
Please like the post if it is useful
Please put to Solved if it resolves your issue.
Thanks for your response. But I have NULL in my input ( like NULL as EmpID from SQL server and outing to an Integer column ) and am creating this JSON tree for MongoDB insert. My Job design is tMSSQLInput-> tMongoDBOutput. The NULL is being converted to "" internally to Job when it tries to convert the input to JSON format.
Do you mean NULL as string or a NULL object. Lot of difference here.
if NULL as string then use below logic
row1.EMPID != null && row1.EMPID.equalsIgnoreCase("NULL") ? new Integer(row1.EMPID.trim()) : Integer.valueOf("")
Have you tried to hand your null object in tMap with ternary operator?
The problem is I want to set the database column value to null. Setting it to 0 is not ok, because there are missing values and there are zero values. Setting it to some arbitrarily large negative value has the same problem. I need to know that I don't know.
Turns out the way to handle this is to set the value to
tWriteJson will set it correctly as a JSON null then.