topic Re: Handling nulls for integer columns while creating JSON for tMongoDBOutput in Talend Studio

Handling nulls for integer columns while creating JSON for tMongoDBOutput

Anonymous — Sat, 16 Nov 2024 09:53:26 GMT

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,

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

Sid3 — Thu, 20 Apr 2017 15:18:03 GMT

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.

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

Anonymous — Fri, 21 Apr 2017 05:23:53 GMT

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,

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

Sid3 — Fri, 21 Apr 2017 09:40:57 GMT

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

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

Anonymous — Fri, 21 Apr 2017 10:04:19 GMT

Its NULL as an object

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

Anonymous — Mon, 18 Sep 2017 19:23:43 GMT

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

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

Anonymous — Thu, 06 Sep 2018 00:04:22 GMT

Same problem here

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

Anonymous — Sun, 30 Sep 2018 10:11:45 GMT

Hello,

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

Best regards

Sabrina

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

Anonymous — Mon, 01 Oct 2018 18:32:36 GMT

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.

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

Anonymous — Wed, 17 Oct 2018 19:20:55 GMT

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.

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

Anonymous — Wed, 18 Dec 2019 17:57:41 GMT

where do i need to set this value?