Talend Connect
Virtual Summit
JOIN US!
And visit the Customer
& Community Lounge.
May 27-28, wherever you are.

tWriteJSONField component can't handle text "null"

Highlighted
Eight Stars

tWriteJSONField component can't handle text "null"

I'm using tWriteJSONField to build JSON from customer data, and I've found a bug.

If someone enters "null" for any text field, it gets written without quotes, and the application that I am posting to rejects it. For example:

    "person": {
        "firstName": "Phil"
        "lastName": "Hibbs"
        "middleName": null
    }
Highlighted
Community Manager

Re: tWriteJSONField component can't handle text "null"

Good find @PhilHibbs 

I'll check to see if this has been raised before. If not, I'll raise it.

A way around this in the meantime (although a pain) is to add a space before or after a "null". 

Highlighted
Community Manager

Re: tWriteJSONField component can't handle text "null"

I see you have already raised it. Thanks :-)

Highlighted
Eight Stars

Re: tWriteJSONField component can't handle text "null"

We'd have to strip the space out afterwards, we can't go changing our client's data because of a deficiency in the tool. Those spaces would show up in the reconciliation & verification and we'd be told to fix it.

If we're going to do that, then we might as well just fix the problem afterwards with a regular expression, something like:

.replaceAll( "(\"[\\w+]\":\\s*)(null)(?=[\\s,\\}\\]])" ,"$1\"$2\"" );

That would have the advantage that it's a one-stop fix for any element being the literal string "null". No need to put special case handling on the input for all the hundreds of values that might contain that string.

The lookahead assertion can probably be simplified, or even removed entirely since the null can't really be followed by anything other than JSON syntax.

 

Highlighted
Community Manager

Re: tWriteJSONField component can't handle text "null"

My suggestion was just an off the cuff quick fix to help you get around this. Your regex solution would probably suit you better, especially since it looks like the application you are using does not accept valid JSON. The assumption being that users are adding "null" for middleName since the application requires a middleName and doesn't accept null.

Highlighted
Eight Stars

Re: tWriteJSONField component can't handle text "null"

Our application accepts valid JSON with any string as middle name - "Bob", "robert", "null", whatever.

Highlighted
Community Manager

Re: tWriteJSONField component can't handle text "null"

I do apologise, I assumed that since "null" was being added to middleName, that it was a weird hack used to indicate no value if the entity does not have a middle name. Since null is valid in JSON, the assumption was that either the application does not accept valid JSON or that it falls over if no value is supplied for middleName.

Highlighted
Eight Stars

Re: tWriteJSONField component can't handle text "null"

Yeah, it's easy to get confused when people are talking about "null" as a value!
Highlighted
Community Manager

Re: tWriteJSONField component can't handle text "null"

Yeah, a problem that we face on here is that some users have more experience than others. I tend to assume a lower level of experience as it ultimately leads to quicker solutions. Having said that, in this case it has led to a far lengthier discussion than was required :-)

 

I'll keep an eye on this Jira to try and get it resolved asap.

 

Thanks for raising it

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog