tWriteJSONField component can't handle text "null"

Highlighted
Seven 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
    }
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". 

Community Manager

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

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

Seven 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.

 

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.

Seven Stars

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

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

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.

Seven 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!
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 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download