Handling nulls for integer columns while creating JSON for tMongoDBOutput

[Anonymous]

Hi, 

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.

Thanks,

sr

[Sid3]

Sr,

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.

Thanks,
Sid
Please like the post if it is useful
Please put to Solved if it resolves your issue.

[Anonymous]

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.

Thanks,

sr

[Sid3]

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

[Anonymous]

Its NULL as an object

[Anonymous]

I am having the same issue, was this ever resolved or a solution found?

[Anonymous]

Same problem here

[Anonymous]

Hello,

Have you tried to hand your null object in tMap with ternary operator?

Best regards

Sabrina

[Anonymous]

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.

[Anonymous]

Turns out the way to handle this is to set the value to

net.sf.json.JSONNull.getInstance()

tWriteJson will set it correctly as a JSON null then.