Replacing / escaping special characters to be used for JSON

[michael_hartmann1]

I have a process that is generating some JSON that is used with a Download tool (using POST HTTP action) to update a database via an API. All is good except there is a required field that has some data that may have special characters in it. I need to replace those special characters with valid escape characters for JSON. I found some documentation about what is valid, but I don't see an easy way to do this. I saw something like this in a tool that escapes special characters for XML, but I don't think that will work.

Does anyone have a handy way to do this? Maybe with some Regex (I don't know Regex, but this seems like a good use case)?

[Joe_Mako · Accepted solution]

Using the list from https://mathiasbynens.be/notes/javascript-escapes

How about something like this:

IF IsNull([String]) THEN '\0' ELSE
Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([String], 
CharFromInt(92),'\\'),
CharFromInt(12),'\f'),
CharFromInt(10),'\n'),
CharFromInt(13),'\r'),
CharFromInt(9),'\t'),
CharFromInt(11),'\v'),
CharFromInt(39),"\'"),
CharFromInt(34),'\"'),
CharFromInt(8),'\b')
ENDIF

[michael_hartmann1]

@Joe_Mako - thanks! I just tried that. It looks as if your formula worked like a champ.

Unfortunately, the post via the API failed from that. I have a request in with the vendor of that API to get some clarification on how to handle...maybe they have a special case. But, I think your solution should work well.

[Joe_Mako]

I also just saw http://stackoverflow.com/a/27516892 so the character codes may need to be edited, or made more sophisticated to handle other characters with the /u escape character, and a single quote may not be a valid character in JSON.

[michael_hartmann1]

I saw that same conversation on stackoverflow.com yesterday (in fact, printed out the diagram). I think this comes down to the specifics about the API I'm trying to interface with. Once I get an answer about that, I think the solution you provided will work well.

Thanks @Joe_Mako

[michael_hartmann1]

Ok, the plot has thickened on this one. Turns out that the real issue was a missing value in a required field. BUT...regarding the special characters, I have used the formula that was provided earlier in this thread - THANKS - and it works great. For new line, it replaces it with "\n" (without the quotes). But, somehow - either in Alteryx / cURL or in the third party API, it is becoming "/n" and that content is being updated on my record. So, if I start with "line 1[newlne char]line2" it is updating to "line1/nline2".

This is not an area of expertise for me, so I'm wondering if anyone has any clues or ideas to get around this. I am trying to coordinate a call wtih Alteryx support and the other vendor, but wanted to see if anyone else had experienced something like this. 

[25priyanka02]

Hello Micheal, 

Did you find a solution to \n issue in the JSON file after download tool use?