Four Stars sr
Four Stars

Handling nulls for integer columns while creating JSON for tMongoDBOutput

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.ConfigureJSONTree.JPG

 

error.JPG

Can anyone assist on this please.

Thanks,

sr

  • Big Data
  • Data Integration
4 REPLIES
Six Stars

Re: Handling nulls for integer columns while creating JSON for tMongoDBOutput

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.

Four Stars sr
Four Stars

Re: Handling nulls for integer columns while creating JSON for tMongoDBOutput

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

Six Stars

Re: Handling nulls for integer columns while creating JSON for tMongoDBOutput

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

Four Stars sr
Four Stars

Re: Handling nulls for integer columns while creating JSON for tMongoDBOutput

Its NULL as an object