How to escape double quotes from the value of a JSON field

Question

Hi All, 
 We are getting data from Sharepoint using a!httpQuery in json format and then converting it to appian value using a!fromJson to use in the application. It all works fine except for the scenario where the value for a json field has some word with double quotes. 
 For Ex: 
 {"Title":"This is an "example" to show the issue", 
 "Name":"Some value", 
 "Created":"9/25/2017 8:49:53 AM" 
 } 
 In the above code snippet, the issue happens as example is in double quotes. We cannot restrict the source system, so we have to take care of it on our end. 
 How can we escape the double quotes here. 
 Any response is appreciated. Thanks in advance.

Jamal Case · Answer

You could use the stripwith() or cleanwith() functions to remove the double quotes. please refer to https://docs.appian.com/suite/help/17.3/fnc_text_cleanwith.html and https://docs.appian.com/suite/help/17.3/fnc_text_stripwith.html

Carlos Santander · Answer

If you know the json fields ahead of time, you could preprocess the text before passing it to a!fromjson(). You could do a search to find "Title", "Name", and "Created", then take the values in between the fields ("This is an "example" to show the issue"), and manually replace the quotation marks in the value. You would then feed the resulting strint to a!fromjson. 
 
I'm inclined to say that this is technically invalid json. You could use one of the many online validation tools out there to make a case that the source system makes a change because it's generating invalid data.

Mike Schmitt · Answer

"We cannot restrict the source system, so we have to take care of it on our end." 
 
 Are you saying that the unescaped text is coming from the DB and you have no control over it? I don't believe it's valid JSON formatting in that case, and unless you're able to cook up some sort of manual parser (checking for any double quotes not either right after a ':' or right before a ',' or EOL, then subbing them out for the escaped equivalent), you might not be able to work with it. 
 Others here have pointed out that proper escaping of a double quote on JSON is a double-double-quote i.e. "", however after testing it out in Appian, it looks like an escaped quote is \". You can try it yourself like this: https://www.screencast.com/t/t3PYmFNaj

How to escape double quotes from the value of a JSON field

Top Replies