Forum Discussion
Data Factory adding extra \ in JSON data
Hi KBD
Great to see the _fixed fields are being generated correctly — looks like it's already working!
One question: how are you seeing the backslashes in the result? Is it in the ADF monitor output panel, when you click on the activity during a debug run?
I ask because that panel serializes everything as JSON, and any quotes inside a string will always show as \". That doesn't necessarily mean the backslashes are actually in the data.
To confirm, try this:
1. In the ADF monitor, click the glasses icon on the activity output. If the "Response" value appears as a string wrapped in quotes, the backslashes are just visual representation.
2. If your function is in Python, add a logging.info() to dump the RAW body it receives and check it in Azure Portal → Function App → Monitor. That will show you what actually arrived, without ADF monitor interference.
3. Quickest check: if req.get_json() didn't throw any exception and the _fixed fields are correct, the JSON arrived clean.
(Apologies for not going into more detail — I'm on my phone and it's a bit tricky to type 😅)
Given that your _fixed fields have the right content, you've most likely already solved it without realising it
If my comments helped solve your question, it would be great if you could like the two comments and mark it as the accepted solution. It helps others with the same issue and also motivates me to keep contributing.
Thanks a lot, I really appreciate it.
AI was used to ensure clarity of wording
Hi KBD
Just to clarify — I wasn't stating anything, I was asking to better understand what was happening. Thanks for sharing the file, it really helps!
I'm trying to figure this out alongside you while giving you options, so take this as ideas to try rather than definitive answers.
On the backslashes in the file — try this: the reason they appear might simply be that the Copy Data activity is writing the response as a text column inside a CSV. CSV format wraps string values in outer quotes and escapes the inner ones as \". That wouldn't be an ADF bug or a problem with your code — it would be the output format itself.
On why Python processed correctly despite the backslashes — that makes sense: Python received the clean JSON directly from the request, before ADF serialized it to write it into the CSV. That's why the _fixed fields are correct. The problem would be downstream, in how the output is being written.
For Power BI you need a clean .json file.
If your function is in Python, I think that the cleanest option would be to write the JSON file directly from the function itself, without going through Copy Data. To verify it first, add a logging.info() to dump the RAW body before any processing — you can check it in Azure Portal → Function App → Monitor → Invocations. Here's the official reference: https://learn.microsoft.com/en-us/azure/azure-functions/functions-reference-python#logging
If my comments helped solve your question, it would be great if you could like the two comments and mark it as the accepted solution. It helps others with the same issue and also motivates me to keep contributing.
Thanks a lot, I really appreciate it.
AI was used to ensure clarity of wording
Hi KBD ,
Thanks for sharing the detailed investigation, this is a great breakdown of the behavior.
From what you’ve described, the pipeline is actually working correctly end to end. The key point is that the backslashes (\) you’re seeing are due to JSON being serialized as a string, not an issue with the actual data itself.
Your Azure Function is successfully processing clean JSON which is why the fixed fields are correct. The escaping is introduced later when the response is handled as a string and written via CSV, where quotes are automatically escaped.
The main recommendation here would be:
Avoid converting JSON to string (string()) unless absolutely required
Ensure the Azure Function returns application/json (not a string payload)
Use a JSON sink instead of CSV in the final Copy Activity
Your workaround with removing quote/escape characters confirms the root cause is output format handling, not data corruption.
At this point, you’re very close, switching the final output to native JSON should resolve this cleanly for downstream Power BI consumption.
Thanks again for documenting the steps so clearly, this will definitely help others facing similar serialization behavior.
I just want to wrap this up with what I found that works.
v-echaithra & arabalca have provided valuable suggestions & feedback.
My Pipeline looks like this:
Lookup_Attrib2Clean loads a JSON file with the attributes to be fixed by file. Look above for a sample,
look for "attributes2clean":[
Lookup_JSON_in loads the JSON file to be processed. We are taking attributes with HTML in them and striping out the HTML. look above at "extractData":[
af_fixAttrib calls and Azue Function that I wrote to strip out the HTML and create new clean attributes.
Passes the attributes2clean and extractData in the body and receives the new JSON in the Response.
Body looks like this:
{ "extract":{ "extractName":"AuditPlan" }, "attrib2clean":{ "attributes2clean":@{activity('Lookup_Attrib2Clean').output.value } }, "eData": { "extractData":@{activity('Lookup_JSON_in').output.value} } }Next Activity pulls in a dummy text file merges it with response and copies out a JSON file.
In additional Columns I have: @{activity('af_FixAttrib').output.Response}
Creates a JSON file but the extra slashes are there. I never converted the response to string.
But the extrat slashes show up.
This does not work. The data required to replicate this issue is posted here.
Would be very interested to see if anybody can get this to work.
Also learned that the Lookup Activity will not load more that 4MB or 500 rows. Are you joking MicroSoft?
I have files to be process that are over 20 MB.
Thank you for your suggestions and kind assistance
KD
PS: My idea of using a CSV file, as the final sink, with "No escape character" and No Quote Character, did not work. When I tried to load that file into Power BI the JSON was damaged. Believe the HTML code in the file was mangled.
PS: this all works in Python with no stress.
KD
