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
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
arabalca:
My pipeline looks like this now
I disagree with the statement that the extra back slashes are NOT actually in the data.
The Set Variable activity expression is:
I added a Copy Data activity, as you can see.
In that file I find
Col1,resp
"Dummy Text File","[{\"prop_id\":2539,\"name\":\"Clean & quiet apt home by the park\",\"property_description\":\"<body lang=EN-US style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>Bright, clean, quiet apt near the park.\u00a0 <\/p><p class=MsoNormal><b>Two blocks to subway<\/b><\/p><p class=MsoNormal>sunny<\/p><\/div><\/body>\",\"host_id\":2787,\"host_name\":\"John\",\"neighbourhood_group\":\"Brooklyn\",\"neighbourhood\":\"Kensington\",\"neighourhood_description\":\"<p class=MsoNormal><u>Kensington<\/u> is a neighborhood in the <b>central portion of the New York City borough of Brooklyn<\/b> <\/p><p class=MsoNormal>located south of Prospect Park and Green-Wood Cemetery. Kensington and Parkville are bordered by the Prospect Park South and Ditmas Park subsections of Flatbush to the east; Windsor Terrace to the north; Borough Park to the west; and Midwood to the south.<\/p> \",\"latitude\":40.64749,\"longitude\":-73.97237,\"room_type\":\"Private room\",\"price\":149,\"minimum_nights\":1,\"number_of_reviews\":9,\"last_review\":\"2018-10-19\",\"reviews_per_month\":0.21,\"calculated_host_listings_count\":6,\"availability_365\":365,\"property_description_fixed\":\"Bright, clean, quiet apt near the park.\u00a0 Two blocks to subwaysunny\",\"neighourhood_description_fixed\":\"Kensington is a neighborhood in the central portion of the New York City borough of Brooklyn located south of Prospect Park and Green-Wood Cemetery. Kensington and Parkville are bordered by the Prospect Park South and Ditmas Park subsections of Flatbush to the east; Windsor Terrace to the north; Borough Park to the west; and Midwood to the south. \"},{\"prop_id\":2595,\"name\":\"Skylit Midtown Castle\",\"property_description\":\"<p class=MsoNormal>It has a <b>sky light<\/b>, you will love it<\/p> \",\"host_id\":2845,\"host_name\":\"Jennifer\",\"neighbourhood_group\":\"Manhattan\",\"neighbourhood\":\"Midtown\",\"neighourhood_description\":\"Mid-town has it all\",\"latitude\":40.75362,\"longitude\":-73.98377,\"room_type\":\"Entire home\/apt\",\"price\":225,\"minimum_nights\":1,\"number_of_reviews\":45,\"last_review\":\"2019-05-21\",\"reviews_per_month\":0.38,\"calculated_host_listings_count\":2,\"availability_365\":355,\"property_description_fixed\":\"It has a sky light, you will love it \",\"neighourhood_description_fixed\":\"Mid-town has it all\"},{\"prop_id\":3647,\"name\":\"THE VILLAGE OF HARLEM....NEW YORK !\",\"property_description\":\"HARLEM - music, culture, history - Black America\",\"host_id\":4632,\"host_name\":\"Elisabeth\",\"neighbourhood_group\":\"Manhattan\",\"neighbourhood\":\"Harlem\",\"neighourhood_description\":\" text right begore Lat & Long \",\"latitude\":40.80902,\"longitude\":-73.9419,\"room_type\":\"Private room\",\"price\":150,\"minimum_nights\":3,\"number_of_reviews\":0,\"last_review\":null,\"reviews_per_month\":null,\"calculated_host_listings_count\":1,\"availability_365\":365,\"property_description_fixed\":\"HARLEM - music, culture, history - Black America\",\"neighourhood_description_fixed\":\"text right begore Lat & Long \"},{\"prop_id\":3831,\"name\":\"Cozy Entire Floor of Brownstone\",\"property_description\":null,\"host_id\":4869,\"host_name\":\"LisaRoxanne\",\"neighbourhood_group\":\"Brooklyn\",\"neighbourhood\":\"Clinton Hill\",\"neighourhood_description\":null,\"latitude\":40.68514,\"longitude\":-73.95976,\"room_type\":\"Entire home\/apt\",\"price\":89,\"minimum_nights\":1,\"number_of_reviews\":270,\"last_review\":\"2019-07-05\",\"reviews_per_month\":4.64,\"calculated_host_listings_count\":1,\"availability_365\":194,\"property_description_fixed\":null,\"neighourhood_description_fixed\":null},{\"prop_id\":5022,\"name\":\"Entire Apt: Spacious Studio\/Loft by central park\",\"property_description\":\"<p class=MsoNormal>type of townhouse or rowhouse, clad in brown sandstone, that is iconic to 19th-century New York City, Boston, and other US cities.<\/p>\",\"host_id\":7192,\"host_name\":\"Laura\",\"neighbourhood_group\":\"Manhattan\",\"neighbourhood\":\"East Harlem\",\"neighourhood_description\":\"Classic East Harlem Brownstone\",\"latitude\":40.79851,\"longitude\":-73.94399,\"room_type\":\"Entire home\/apt\",\"price\":80,\"minimum_nights\":10,\"number_of_reviews\":9,\"last_review\":\"2018-11-19\",\"reviews_per_month\":0.1,\"calculated_host_listings_count\":1,\"availability_365\":0,\"property_description_fixed\":\"type of townhouse or rowhouse, clad in brown sandstone, that is iconic to 19th-century New York City, Boston, and other US cities.\",\"neighourhood_description_fixed\":\"Classic East Harlem Brownstone\"},{\"prop_id\":5099,\"name\":\"Large Cozy 1 BR Apartment In Midtown East\",\"property_description\":\"<body lang=EN-US link=\"#467886\" vlink=\"#96607D\" style='word-wrap:break-word'><div class=WordSection1><h1>A header<\/h1><p class=MsoNormal>area between 42nd and 59th Streets, east of 5th Avenue to the East River.\u00a0 Includes <b>Turtle Bay<\/b>, Tudor City, and areas around Grand Central Terminal.<\/p><\/div><\/body>\",\"host_id\":7322,\"host_name\":\"Chris\",\"neighbourhood_group\":\"Manhattan\",\"neighbourhood\":\"Murray Hill\",\"neighourhood_description\":\"Nothing here\",\"latitude\":40.74767,\"longitude\":-73.975,\"room_type\":\"Entire home\/apt\",\"price\":200,\"minimum_nights\":3,\"number_of_reviews\":74,\"last_review\":\"2019-06-22\",\"reviews_per_month\":0.59,\"calculated_host_listings_count\":1,\"availability_365\":129,\"property_description_fixed\":\"A headerarea between 42nd and 59th Streets, east of 5th Avenue to the East River.\u00a0 Includes Turtle Bay, Tudor City, and areas around Grand Central Terminal.\",\"neighourhood_description_fixed\":\"Nothing here\"},{\"prop_id\":5121,\"name\":\"BlissArtsSpace!\",\"property_description\":null,\"host_id\":7356,\"host_name\":\"Garon\",\"neighbourhood_group\":\"Brooklyn\",\"neighbourhood\":\"Bedford-Stuyvesant\",\"neighourhood_description\":null,\"latitude\":40.68688,\"longitude\":-73.95596,\"room_type\":\"Private room\",\"price\":60,\"minimum_nights\":45,\"number_of_reviews\":49,\"last_review\":\"2017-10-05\",\"reviews_per_month\":0.4,\"calculated_host_listings_count\":1,\"availability_365\":0,\"property_description_fixed\":null,\"neighourhood_description_fixed\":null},{\"prop_id\":5178,\"name\":\"Large Furnished Room Near B'way \",\"property_description\":null,\"host_id\":8967,\"host_name\":\"Shunichi\",\"neighbourhood_group\":\"Manhattan\",\"neighbourhood\":\"Hell's Kitchen\",\"neighourhood_description\":null,\"latitude\":40.76489,\"longitude\":-73.98493,\"room_type\":\"Private room\",\"price\":79,\"minimum_nights\":2,\"number_of_reviews\":430,\"last_review\":\"2019-06-24\",\"reviews_per_month\":3.47,\"calculated_host_listings_count\":1,\"availability_365\":220,\"property_description_fixed\":null,\"neighourhood_description_fixed\":null},{\"prop_id\":5203,\"name\":\"Cozy Clean Guest Room - Family Apt\",\"property_description\":null,\"host_id\":7490,\"host_name\":\"MaryEllen\",\"neighbourhood_group\":\"Manhattan\",\"neighbourhood\":\"Upper West Side\",\"neighourhood_description\":null,\"latitude\":40.80178,\"longitude\":-73.96723,\"room_type\":\"Private room\",\"price\":79,\"minimum_nights\":2,\"number_of_reviews\":118,\"last_review\":\"2017-07-21\",\"reviews_per_month\":0.99,\"calculated_host_listings_count\":1,\"availability_365\":0,\"property_description_fixed\":null,\"neighourhood_description_fixed\":null}]"
Lots of extra back slashes. So to me the extra back slashes are in the data.
The fixed attribute data is correct, so some how my Python code did its job. strange.
Need to get rid of the back slashes and output to a JSON file.
Down stream Power BI is expecting JSON file
KD
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
I believe we have made progress. Following up on what you said above about CSV format wraps string values in outer quotes and escapes the inner ones as \".
On my final Copy Activity, in the Data Set the output CSV file I set "No escape character" and No Quote Character.
Now in my out put file file I see:
Col1|resp Dummy Text File|[{"prop_id":2539,"name":"Clean & quiet apt home by the park","property_description":"<body lang=EN-US style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>Bright, clean, quiet apt near the park.\u00a0 <\/p><p class=MsoNormal><b>Two blocks to subway<\/b><\/p><p class=MsoNormal>sunny<\/p><\/div><\/body>","host_id":2787,"host_name":"John","neighbourhood_group":"Brooklyn","neighbourhood":"Kensington","neighourhood_description":"<p class=MsoNormal><u>Kensington<\/u> is a neighborhood in the <b>central portion of the New York City borough of Brooklyn<\/b> <\/p><p class=MsoNormal>located south of Prospect Park and Green-Wood Cemetery. Kensington and Parkville are bordered by the Prospect Park South and Ditmas Park subsections of Flatbush to the east; Windsor Terrace to the north; Borough Park to the west; and Midwood to the south.<\/p> ","latitude":40.64749,"longitude":-73.97237,"room_type":"Private room","price":149,"minimum_nights":1,"number_of_reviews":9,"last_review":"2018-10-19","reviews_per_month":0.21,"calculated_host_listings_count":6,"availability_365":365,"property_description_fixed":"Bright, clean, quiet apt near the park.\u00a0 Two blocks to subwaysunny","neighourhood_description_fixed":"Kensington is a neighborhood in the central portion of the New York City borough of Brooklyn located south of Prospect Park and Green-Wood Cemetery. Kensington and Parkville are bordered by the Prospect Park South and Ditmas Park subsections of Flatbush to the east; Windsor Terrace to the north; Borough Park to the west; and Midwood to the south. "},{"prop_id":2595,"name":"Skylit Midtown Castle","property_description":"<p class=MsoNormal>It has a <b>sky light<\/b>, you will love it<\/p> ","host_id":2845,"host_name":"Jennifer","neighbourhood_group":"Manhattan","neighbourhood":"Midtown","neighourhood_description":"Mid-town has it all","latitude":40.75362,"longitude":-73.98377,"room_type":"Entire home\/apt","price":225,"minimum_nights":1,"number_of_reviews":45,"last_review":"2019-05-21","reviews_per_month":0.38,"calculated_host_listings_count":2,"availability_365":355,"property_description_fixed":"It has a sky light, you will love it ","neighourhood_description_fixed":"Mid-town has it all"},{"prop_id":3647,"name":"THE VILLAGE OF HARLEM....NEW YORK !","property_description":"HARLEM - music, culture, history - Black America","host_id":4632,"host_name":"Elisabeth","neighbourhood_group":"Manhattan","neighbourhood":"Harlem","neighourhood_description":" text right begore Lat & Long ","latitude":40.80902,"longitude":-73.9419,"room_type":"Private room","price":150,"minimum_nights":3,"number_of_reviews":0,"last_review":null,"reviews_per_month":null,"calculated_host_listings_count":1,"availability_365":365,"property_description_fixed":"HARLEM - music, culture, history - Black America","neighourhood_description_fixed":"text right begore Lat & Long "},{"prop_id":3831,"name":"Cozy Entire Floor of Brownstone","property_description":null,"host_id":4869,"host_name":"LisaRoxanne","neighbourhood_group":"Brooklyn","neighbourhood":"Clinton Hill","neighourhood_description":null,"latitude":40.68514,"longitude":-73.95976,"room_type":"Entire home\/apt","price":89,"minimum_nights":1,"number_of_reviews":270,"last_review":"2019-07-05","reviews_per_month":4.64,"calculated_host_listings_count":1,"availability_365":194,"property_description_fixed":null,"neighourhood_description_fixed":null},{"prop_id":5022,"name":"Entire Apt: Spacious Studio\/Loft by central park","property_description":"<p class=MsoNormal>type of townhouse or rowhouse, clad in brown sandstone, that is iconic to 19th-century New York City, Boston, and other US cities.<\/p>","host_id":7192,"host_name":"Laura","neighbourhood_group":"Manhattan","neighbourhood":"East Harlem","neighourhood_description":"Classic East Harlem Brownstone","latitude":40.79851,"longitude":-73.94399,"room_type":"Entire home\/apt","price":80,"minimum_nights":10,"number_of_reviews":9,"last_review":"2018-11-19","reviews_per_month":0.1,"calculated_host_listings_count":1,"availability_365":0,"property_description_fixed":"type of townhouse or rowhouse, clad in brown sandstone, that is iconic to 19th-century New York City, Boston, and other US cities.","neighourhood_description_fixed":"Classic East Harlem Brownstone"},{"prop_id":5099,"name":"Large Cozy 1 BR Apartment In Midtown East","property_description":"<body lang=EN-US link="#467886" vlink="#96607D" style='word-wrap:break-word'><div class=WordSection1><h1>A header<\/h1><p class=MsoNormal>area between 42nd and 59th Streets, east of 5th Avenue to the East River.\u00a0 Includes <b>Turtle Bay<\/b>, Tudor City, and areas around Grand Central Terminal.<\/p><\/div><\/body>","host_id":7322,"host_name":"Chris","neighbourhood_group":"Manhattan","neighbourhood":"Murray Hill","neighourhood_description":"Nothing here","latitude":40.74767,"longitude":-73.975,"room_type":"Entire home\/apt","price":200,"minimum_nights":3,"number_of_reviews":74,"last_review":"2019-06-22","reviews_per_month":0.59,"calculated_host_listings_count":1,"availability_365":129,"property_description_fixed":"A headerarea between 42nd and 59th Streets, east of 5th Avenue to the East River.\u00a0 Includes Turtle Bay, Tudor City, and areas around Grand Central Terminal.","neighourhood_description_fixed":"Nothing here"},{"prop_id":5121,"name":"BlissArtsSpace!","property_description":null,"host_id":7356,"host_name":"Garon","neighbourhood_group":"Brooklyn","neighbourhood":"Bedford-Stuyvesant","neighourhood_description":null,"latitude":40.68688,"longitude":-73.95596,"room_type":"Private room","price":60,"minimum_nights":45,"number_of_reviews":49,"last_review":"2017-10-05","reviews_per_month":0.4,"calculated_host_listings_count":1,"availability_365":0,"property_description_fixed":null,"neighourhood_description_fixed":null},{"prop_id":5178,"name":"Large Furnished Room Near B'way ","property_description":null,"host_id":8967,"host_name":"Shunichi","neighbourhood_group":"Manhattan","neighbourhood":"Hell's Kitchen","neighourhood_description":null,"latitude":40.76489,"longitude":-73.98493,"room_type":"Private room","price":79,"minimum_nights":2,"number_of_reviews":430,"last_review":"2019-06-24","reviews_per_month":3.47,"calculated_host_listings_count":1,"availability_365":220,"property_description_fixed":null,"neighourhood_description_fixed":null},{"prop_id":5203,"name":"Cozy Clean Guest Room - Family Apt","property_description":null,"host_id":7490,"host_name":"MaryEllen","neighbourhood_group":"Manhattan","neighbourhood":"Upper West Side","neighourhood_description":null,"latitude":40.80178,"longitude":-73.96723,"room_type":"Private room","price":79,"minimum_nights":2,"number_of_reviews":118,"last_review":"2017-07-21","reviews_per_month":0.99,"calculated_host_listings_count":1,"availability_365":0,"property_description_fixed":null,"neighourhood_description_fixed":null}]Notice the extra back slashes (\) are gone.
But this a CSV file with | as separater. the second col is the JSON.
How do get my JSON into a proper JSON file?
Many thanks for your patience and kind assistance
KBD
Hi KBD ,
Great!
Now for the final step — try this: in your Copy Data activity, instead of using a DelimitedText dataset as the sink, change it to a JSON dataset pointing to your Data Lake. It should write the response content directly as a valid .json file, with no CSV involved.
More: https://learn.microsoft.com/en-us/fabric/data-factory/format-json
Almost there!
If my comments helped solve your question, it would be great if you could like the all comment and mark it as the accepted solutions. It helps others with the same issue and also motivates me to keep contributing.
Thanks a lot, I really appreciate it.