Validate and clean up JSON in Omnis Studio?

Question

A correct JSON is

[{
    "user_id": 1,
    "user_name": "John Doe I"
}, {
    "user_id": 2,
    "user_name": "Jane Doe III"
}]

But if it contains some illegal characters it will not validate. Like this with a CR after Doe:

[{"user_id":1,"user_name":"John Doe 
I"},{"user_id":2,"user_name":"Jane Doe III"}]

My question is if there is a "clean up" function in Omnis Studio 8 where the output is a correct JSON?

EDIT

To replace or delete KNOWN characters is easy. The problem is that text copied from MS Word and the Web can contain UNKNOWN characters. So I am searching for a command like

Calculate VALIDJSON as keepvalidchar(NOTVALIDJSON)

Is there such a beast?

Answer 1

Are you simply looking for a

do replaceall(lcVar,kCR,'\n') Returns lcVar

function call?

Thus replacing the CR character with the escaped normal encoding of '\n'

I guess the other question is, are you creating the JSON to send, or receiving the JSON and trying to decode it?

If receiving, maybe OJSON.$formatjson() may help?

Answer 2

according to this post How do I handle newlines in JSON?

if you want newlines in a text string, you need to escape the \n. so \n in a the original json string should be valid replacement for a 'cr'. eg.

[{"user_id":1,"user_name":"John Doe\nI"},{"user_id":2,"user_name":"Jane Doe III"}]

My read on this is that if you are pulling the data out of a database to send some place on the web, you might want to escape the newline to retain it in the text, rather than altering the JSON after the fact. Of course, that depends on the purpose of the API call to the server receiving the json.

I guess the crux is

• if you want the json to reflect the original text content including special characters, you escape it
• if you don't, then you remove it. I'd probably clean the original source since I might want to leave the json formatted and doing a replace of \n for blank .. might not have the effect you want.