Solved: Re: Data Factory adding extra \ in JSON data

KBD

Hello:

In Azure Data Factory, I am working with JSON data in order to send a request to an Azure function.

I need to create the Body of an HTML request that contains properly formatted

JSON that looks likes, HTML body below.

Must have the sections: extract, attrib2clean & eData, the Azure function is expecting them.

Data for attrib2clean and eData will be read in from JSON files on Data Lake Gen 2.

Problem:

When I read in attrib2clean using a Lookup activity the output is as follows:

{
	"count": 6,
	"value": [
		{
			"Extract": "EscapePlan",
			"Attrib": "Escape_Plan_Field",
			"NewAttrib": "Escape_Plan_Field_fixed"
		},
		{
			"Extract": "EscapePlan",
			"Attrib": "Escape_Plan_Description",
			"NewAttrib": "Escape_Plan_Description_fixed"
		},
		{
			"Extract": "AB_NYC",
			"Attrib": "property_description",
			"NewAttrib": "property_description_fixed"
		},
		{
			"Extract": "plug",
			"Attrib": "attrib2",
			"NewAttrib": "attrbit2_fixed"
		},
		{
			"Extract": "AB_NYC",
			"Attrib": "neighourhood_description",
			"NewAttrib": "neighourhood_description_fixed"
		},
		{
			"Extract": "plug",
			"Attrib": "attrib1",
			"NewAttrib": "attrbit1_fixed"
		}
	],
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		],
		"totalBillableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	},
	"durationInQueue": {
		"integrationRuntimeQueue": 0
	}
}

This good.

Next I use a Set Variable to extract the value: node.

Looks like this: @string( activity('Lookup_Attrib').output.value )

This where things go sideways.

This step results in:

{
	"name": "attrib2clean",
	"value": "[{\"Extract\":\"EscapePlan\",\"Attrib\":\"Escape_Plan_Field\",\"NewAttrib\":\"Escape_Plan_Field_fixed\"},{\"Extract\":\"EscapePlan\",\"Attrib\":\"Escape_Plan_Description\",\"NewAttrib\":\"Escape_Plan_Description_fixed\"},{\"Extract\":\"AB_NYC\",\"Attrib\":\"property_description\",\"NewAttrib\":\"property_description_fixed\"},{\"Extract\":\"plug\",\"Attrib\":\"attrib2\",\"NewAttrib\":\"attrbit2_fixed\"},{\"Extract\":\"AB_NYC\",\"Attrib\":\"neighourhood_description\",\"NewAttrib\":\"neighourhood_description_fixed\"},{\"Extract\":\"plug\",\"Attrib\":\"attrib1\",\"NewAttrib\":\"attrbit1_fixed\"}]"
}

As you have noticed all the " have become \".

There are many postings on the web stating that Data Factory does this a sometimes. Other posting say it is an illusion, that

\" is just being displayed and is not really in the data.

In my case I believe the \" are in the data, because if I Copy out to a file they are there.

Next I have another Set Variable to replace the \" with ".

As follows: @replace( variables('attrib2clean') , '\\\\"' , '"' )

For the string to find I have used '\"' , '\\"' & '\\\\"', as suggested by various postings.

No luck. No progress

Need help processing the JSON file so I can put together my HTML PUT request.

The extra \ are killing me.

Thanks you for your attention to this matter.

KD

HTML body

{
  
	"extract":  {
		"extractName":"AB_NYC"
	}  ,
  
	"attrib2clean": {
	   "attributes2clean":[
        {"Extract":"AuditPlan","Attrib":"Audit_Plan_Field", "NewAttrib":"Audit_Plan_Field_fixed"},
		{"Extract":"AuditPlan","Attrib":"Audit_Plan_Description", "NewAttrib":"Audit_Plan_Description_fixed" },
		{"Extract":"AB_NYC", "Attrib":"property_description", "NewAttrib":"property_description_fixed"},
		{"Extract":"plug", "Attrib":"attrib2", "NewAttrib":"attrbit2_fixed"},
		{"Extract":"AB_NYC", "Attrib":"neighourhood_description", "NewAttrib":"neighourhood_description_fixed"},
		{"Extract":"plug", "Attrib":"attrib1", "NewAttrib":"attrbit1_fixed"}    
	]

  }, 

	"eData": {
	    "extractData":
		[
			{
				"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
			},
			{
				"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
			},
			{
				"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
			},
			{
				"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
			},
			{
				"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
			},
			{
				"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
			},
			{
				"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
			},
			{
				"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
			},
			{
				"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
			}
		]
	}		
}

arabalca

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.

View solution in original post

arabalca

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.

View solution in original post

v-echaithra

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.

KBD

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

KBD

continued

Output from af_FixAttrib

{
	"Response": "[{\"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}]",
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
	"executionDuration": 0,
	"durationInQueue": {
		"integrationRuntimeQueue": 0
	},
	"billingReference": {
		"activityType": "ExternalActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	}
}

Notice Response is populated. Attributes property_description_fixed & neighourhood_description_fixed are created and populated.

Questions: How did this work? the extra \ did not confuse the Python code in the function?

Ignoring the extra \ the response looks correct.

The suggestion of using "direct injection" , do not convert to a string, in to the Azure Function body did not eliminate the extra \.

Does anybody have any ideas?

Microsoft why is this complicated?

KD

KBD

arabalca:

I followed up with your suggestion:

Now have a pipeline that looks like this:

Nice and simple.

Output of Lookup_Attrib2Clean:

{
	"count": 6,
	"value": [
		{
			"Extract": "EscapePlan",
			"Attrib": "Escape_Plan_Field",
			"NewAttrib": "Escape_Plan_Field_fixed"
		},
		{
			"Extract": "EscapePlan",
			"Attrib": "Escape_Plan_Description",
			"NewAttrib": "Escape_Plan_Description_fixed"
		},
		{
			"Extract": "AB_NYC",
			"Attrib": "property_description",
			"NewAttrib": "property_description_fixed"
		},
		{
			"Extract": "plug",
			"Attrib": "attrib2",
			"NewAttrib": "attrbit2_fixed"
		},
		{
			"Extract": "AB_NYC",
			"Attrib": "neighourhood_description",
			"NewAttrib": "neighourhood_description_fixed"
		},
		{
			"Extract": "plug",
			"Attrib": "attrib1",
			"NewAttrib": "attrbit1_fixed"
		}
	],
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		],
		"totalBillableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	},
	"durationInQueue": {
		"integrationRuntimeQueue": 0
	}
}

Output of Lookup_AB_NYC:

{
	"count": 9,
	"value": [
		{
			"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.  </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
		},
		{
			"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
		},
		{
			"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
		},
		{
			"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
		},
		{
			"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
		},
		{
			"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.  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
		},
		{
			"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
		},
		{
			"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
		},
		{
			"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
		}
	],
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		],
		"totalBillableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	},
	"durationInQueue": {
		"integrationRuntimeQueue": 10
	}
}

Looks Good

Now lets see with the Azure Function did

body is as follows:

{

"extract":{

"extractName":"AB_NYC"

},

"attrib2clean":{

"attributes2clean":@{activity('Lookup_Attrib2Clean').output.value }

},

"eData": {

"extractData":@{activity('Lookup_AB_NYC').output.value}

}

Input to af_FixAttrib

{
    "functionName": "http_cleanAttribute_test",
    "body": "{\n  \"extract\":{\n     \"extractName\":\"AB_NYC\"\n  },\n  \"attrib2clean\":{\n     \"attributes2clean\":[{\"Extract\":\"EscapePlan\",\"Attrib\":\"Escape_Plan_Field\",\"NewAttrib\":\"Escape_Plan_Field_fixed\"},{\"Extract\":\"EscapePlan\",\"Attrib\":\"Escape_Plan_Description\",\"NewAttrib\":\"Escape_Plan_Description_fixed\"},{\"Extract\":\"AB_NYC\",\"Attrib\":\"property_description\",\"NewAttrib\":\"property_description_fixed\"},{\"Extract\":\"plug\",\"Attrib\":\"attrib2\",\"NewAttrib\":\"attrbit2_fixed\"},{\"Extract\":\"AB_NYC\",\"Attrib\":\"neighourhood_description\",\"NewAttrib\":\"neighourhood_description_fixed\"},{\"Extract\":\"plug\",\"Attrib\":\"attrib1\",\"NewAttrib\":\"attrbit1_fixed\"}]\n   },\n   \"eData\":  {\n       \"extractData\":[{\"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.  </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},{\"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},{\"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},{\"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},{\"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},{\"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.  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},{\"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},{\"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},{\"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}]\n   }  \n  \n}\n",
    "headers": {
        "Accept": "application/json,text/html,application/xhtml+xml,application/xml",
        "Content-Type": "'application/json'"
    },
    "method": "POST"
}

Notice all the extra "\" extra black slashes.

Posting continues.....

KD

arabalca

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.

KBD

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:

@replace(activity('af_FixAttrib').output.response , '\"' , '"')

But does nothing. No help

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

arabalca

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.

KBD

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

arabalca

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.

KBD

arabalca:

Thanks for your kind assistance.

If I use a Copy activity to copy out the response to a CSV file and on the Sink specify "No escape character" and No Quote Character. Then I get a CSV file that looks like this:

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",........

If I copy out to JSON file I get

{"Col1":"Dummy Text File","resp":"[{\"Audit_Plan_Id\":248651,\"Audit_Plan_Completion_Date\":null,\"

notice the extra \.

The CSV file above, on the face of it, is closest to what I need.

However, when I pull the data, CSV or JSON, into Power BI and attempt to parse in Power Query, I get assorted errors.

The issue is that there is HTML code in the data I am processing. That needs to be cleaned up, stripped out, before being report on.

I have the full process work on my desktop in Python. Python does not add extra \.

Believe I may have to create an Azure function that doe the full cleanup.

Including reading and writing the files. Thought I could avoid that.

Many thanks for your assistance.

KD

arabalca

Hi @KBD ,

If you want to go the Azure Function route, you can apply the full process directly inside the function itself, as we discussed — reading, cleaning the HTML, and writing the clean JSON to Data Lake, all from Python.

But if you're working in Fabric, the quickest and cleanest option would be to take the Python you already have working locally and move it into a Fabric Notebook. Process it there, write the result as a Delta table in the Lakehouse, and Power BI consumes it directly. No escaping issues, no CSV, no intermediate steps.

If my comment helped solve your question, it would be great if you could like the comment 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.

tayloramy

Hi @KBD,

This is just string escaping.

Because your JSON is stored in a string in PowerQuery, it can't contain " or that would end the string. So an escape character is used, \, to tell Power Query that "this " is part of the string, not an instruction to end the string"

If you found this helpful, consider giving some Kudos.
If I answered your question or solved your problem, mark this post as the solution!
Join the Fabric Discord!

Proud to be a Super User!

KBD

Working in Azure data Factory. Do not understand where Power Query came from.

KD

KBD

OK this is interesting. I will research and test this.

One question comes to mind immediatly.

How will I handle the response?

The fixed data will be returned in the HTML response,

need to save this data to a JSON file.

Afte the Azue Function call how do I save the response to a file.

Thanks for your attention to this matter.

KD

arabalca

Hi @KBD ,

Have you tried building the request body directly in the Web/Azure Function activity using @{} interpolation instead of string()?

I think that if you Instead of converting the Lookup output to a string variable (which is what causes the escaping), you can try eference the array directly in the Body field like this:

{
"extract": {
"extractName": "AB_NYC"
},
"attrib2clean": {
"attributes2clean": @{activity('Lookup_Attrib').output.value}
},
"eData": {
"extractData": @{activity('Lookup_eData').output.value}
}
}

The @{...} syntax (with curly braces) tells ADF to inject the value as an object rather than serializing it as a string, so the backslashes never appear in the first place.

The root cause is that string() forces ADF to serialize the array into a JSON string, which escapes all inner quotes as \". Skipping that step entirely avoids the problem without needing any replace() workaround.

In other occasions, I tried hash and base64 but I think that in this case isn't applicable.

If my comment helped solve your question, it would be great if you could like the comment 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.