How to remove unwanted \ from JSON output

[ClaytonA]

I am trying to remove unwanted characters from my JSON output file.

Value before RegEx_Replace = "{\"Test\":\"Attribute1\"}"

Instead I would like to output "{"Test":"Attribute"}" or {"Test":"Attribute"} for the value part of the JSON.

When I try to remove the \" and just put a single " (double quote), Alteryx automatically puts a \" to replace it.

I am assuming this is just the formatting of the JSON but need to remove the \'s to achieve one of the desired outputs above. 

Please let me know if I am doing something incorrect.

Thank you for your help in advance. 

[Felipe_Ribeir0]

Hi @ClaytonA 

Have you tried to use the replace function

[IraWatt]

Hey @ClaytonA,

The reason it adds the \ when outputting to JSON as it is escaping the quotes character to make it valid JSON. In order to output to JSON you can structure your data in a table format first:

The output tool essentially follows the same rules as the JSON build tool.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[ClaytonA]

Hi @Felipe_Ribeir0

Sadly I have, but I get the same result when I use the Replace function. The \'s still show up when replacing them with a single " (double quote).

I have also tried using the RegEx Parse Tool as well with the same end result. 

[IraWatt]

Hey @ClaytonA,

An Alternative solution is to output to CSV and overwrite it as a JSON file, this lets you do what you want (make sure to copy these output settings exactly):

[ClaytonA]

---

Hey @IraWatt,

When I export to csv and overwrite with json, it eliminates all formatting of my json so I don't believe this will work. 

Sample of desired output:

[

{

"UserID": 123,

"Value": "{"Test":"Attribute"}"

}, ...

]

So far I am grouping by UserID and pulling in there attributes/values in the json. 

I am able to replace \" with a single ' but not ".

Let me know if you have any other ideas. 

[IraWatt]

AH great point @ClaytonA, Flat file seems to fix the problem:

I added the AutoField as it was complaining about the string size. 

[ClaytonA]

---

Hey @IraWatt,

I tried the exporting to Flat file and overwriting with a .json file path, but I am still coming up with some of the missing {} in my formatting. 

Is this able to be accomplished another way?

[IraWatt]

@ClaytonA I have been giving this a think and it flat files have a size limit which can cause the data to be truncated. It turns out you can use the CSV output with these settings (check example attacked) and overwrite the file name with .JSON:

[ClaytonA]

---

Hey @IraWatt,

I believe the issue is when I am using the JSON Build tool and the appropriate formatting for JSON is not able to be passed when saving the file as a .csv or .txt even though I am overwriting it with a .json format. 

Attached is the workflow I am trying to test and achieve the appropriate results. 

Let me know your thoughts.