How to get rid of backslash "\" and "\r\n" from a JSON file?

Question

I am trying to move data from a JSON file to SQL table, and apparently, there are unnecessary characters ( \ \r\n) that I have to delete in order to map to SQL table using Azure Data Factory.

This is an error message:

I am not sure whether it would help, but I took care of backslash issue on other Pipeline - inside a "Copy data" activity where originally this file was created as a text file.
These are two areas that I made the changes (shown below):

When I validated the JSON file at https://jsonlint.com/, it shows as a "VALID JSON".

I am sharing the JSON file at this URL that having an issue for your reference.

Where/how do I take care of this issue in ADF (Azure Data Factory)?

Answer 1

Hi @KingJava ,
Thanks for posting question on Microsoft Q&A platform. Could you please confirm the other pipeline which you are mentioning about is same as this query that you posted earlier.

As per my understanding, both the scenarios are using same file named : 'GLDETAIL_output.json' . The error is occurring because the data has landed in JSON file with '\r\n' because of which it won't be treated as a valid JSON file by ADF.

To handle that, we need to make sure the invalid characters are removed before landing to .json file.

Could you please try using the below code in the set variable activity that you used after Azure function

@replace(replace(string(activity('APBILL').output.Response),'\r\n',''),'\','')  

Hopefully, this will remove the unwanted characters before hand and we should possibly be getting a valid JSON that will avoid the error.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

@AnnuKumari-MSFT
Thank you so much for your feedback and help.
I used your dynamic expression, and it seems to be working much better, but I have one more issue to take care in order to map to SQL table.

This is expression that I used:
@replace(replace(string(activity('Web1').output.output),'\r\n',''),'\','')
(This pipeline is different from other one, so I have little different expression)

Output of JSON file came as like this:
[" { "GLDETAIL": { "RECORDNO": "249125-1688259-784668--accrual", "BATCH_DATE": "01/01/2022" } }, { "GLDETAIL": { "RECORDNO": "249125-1688260-784669--accrual", "BATCH_DATE": "01/01/2022" } },"]

I am trying to get rid of first " that appears right after [ and " at the end before ] - highlighted in yellow:

How do I go about doing it?

This is details of Web1:

[
"\r\n {\r\n \"GLDETAIL\": {\r\n \"RECORDNO\": \"249125-1688259-784668--accrual\",\r\n \"BATCH_DATE\": \"01/01/2022\"\r\n }\r\n },\r\n {\r\n \"GLDETAIL\": {\r\n \"RECORDNO\": \"249125-1688260-784669--accrual\",\r\n \"BATCH_DATE\": \"01/01/2022\"\r\n }\r\n }\r\n,"
],

This is details of myVariable:
"[\" { \"GLDETAIL\": { \"RECORDNO\": \"249125-1688259-784668--accrual\", \"BATCH_DATE\": \"01/01/2022\" } }, { \"GLDETAIL\": { \"RECORDNO\": \"249125-1688260-784669--accrual\", \"BATCH_DATE\": \"01/01/2022\" } },\"]"

Thanks.