Can't Remove Special characters from json

[#1]

Hi,

I would like to convert this json to csv. But it failed because of the special characters in the json content. Below is the json i would like to convert to csv. So the responseMessage value is where the problem is & its auto-generated in that format. 

Thank you.

INPUT

{

"responseMessage": "hdhdhdhdh:dgdgdgdg "fdfdf" bbbbbbb",

"result": "failed",

"code": "34"

}

DESIRED OUTPUT

{

"responseMessage": "hdhdhdhdh dgdgdgdg fdfdf bbbbbbb",

"result": "failed",

"code": "34"

}

[#2]

@rafy 

The extra character in the response message is not a special character. It's just some extra double-quotes. However, when double-quotes appear in a string in JSON they must be escaped. The correct valid JSON would've been:

{
  "responseMessage": "hdhdhdhdh:dgdgdgdg \"fdfdf\" bbbbbbb",
  "result": "failed",
  "code": "34"
}

The application that's generating those messages should be fixed to generate valid JSON representations.

Cheers

André

[#3]

Thanks so much.

But i have no control over the system generated json.

[#4]

@rafy ,

This is tricky and whatever solution we come up with here cannot be guaranteed to always work. It will always depend on the content that comes in the responseMessage field.

For the particular example that you shared above, this works:

The settings are: 

Search Value:      (?s)(.*\"responseMessage\"\s*:\s*\"[^\"]*|\G[^\"]*)\"(?!,)
Replacement Value: $1\\"
Evaluation Mode:   Entire text

You can check it out here: https://regex101.com/r/paPPhK/1

Cheers,

André