Azure Data Factory set variable add backslash before double quote.

Mehrsa 5

In Azure Data Factory, I need to copy the output of a REST API, which is an array of nested JSONs, to a CSV file. I used a loop for the array and dynamic content for the copy activity's mapping.

I have a Pipelines_Mappings variable, it contains a JSON string with double quotes. Inside the loop, there is a set variable activity that manipulates this variable and changes the index in the string based on the loop index and assigns it to the Modified_Mappings variable. then I use the modified string, which is in the Modified_Mappings variable, in the copy activity. I set the mapping tab of the copy activity as @json(Modified_Mappings).

When I use the set variable to manipulate the json string, backslashes are added before each double quote. This causes issues with the JSON and the copy doesn't work correctly. How can I remove these backslashes? Here is part of the Pipelines_Mappings variable
"value": ""value": {\t{ "type": "TabularTranslator",

"source": {
					"path": "$['results'][@{item()}]['id']"
				},

Amira Bedhiafi 24,636 Reputation points

2023-09-22T15:38:11.86+00:00
To remove the backslashes that are being added before each double quote in the JSON string when you use the set variable activity in ADF, you can use an expression:

replace(variables('Pipelines_Mappings'), '\\"', '"')

Enter it into the Value field of the Set Variable activity like below :

Set Variable: Name: Modified_Mappings Value: replace(variables('Pipelines_Mappings'), '\\"', '"')

For example, if you are copying the JSON string to a CSV file, you can use the following mapping:

@json(Modified_Mappings)
Mehrsa 5 Reputation points

2023-09-25T07:47:37.8866667+00:00

Hi Amira,

I tried it, but it didn't work.
KranthiPakala-MSFT 46,492 Reputation points Microsoft Employee

2023-10-06T01:05:21.7766667+00:00

@Mehrsa Have you tried converting it from string type to json type using @json() function? This should automatically remove escape characters.

If you have tried it yet, please try and let me know how it goes.

A similar topic has been discussed in the stackoverflow thread (please review) : How to remove escape character from the output of Lookup activity in Azure Data Factory?

Hope this info helps.

Thank you.
Amira Bedhiafi 24,636 Reputation points

2023-10-06T08:41:53.57+00:00
Let's try double escaping ? Try and get back to us

Set Variable: Name: Modified_Mappings Value: replace(variables('Pipelines_Mappings'), '\\\"', '\"')
Mehrsa 5 Reputation points

2023-10-09T12:50:16.84+00:00
@KranthiPakala-MSFT
I've created a very simple function in the SQL DB as following:

CREATE FUNCTION [dbo].[Manipulate_String_03]() RETURNS nvarchar(500) AS BEGIN DECLARE @Result_Str nvarchar(500) = '{"id":2, "info":{"name":"John", "surname":"Smith"}, "age":25}' RETURN @Result_Str END

Then I called the function from the lookup activity

There is an array variable called myArray, that has been set in the Append Variable activity as follows:

@json(activity('Lookup1').output.firstRow)

but, I got the following error for the append variable activity
Error code: InvalidTemplate

Failure type: User configuration issue

Details:The function 'json' expects its parameter to be a string or an XML. The provided value is of type 'Object'.
Mehrsa 5 Reputation points

2023-10-09T13:18:07.3766667+00:00

@Amira Bedhiafi
Replacing the backslash doesn't work, I tried with different numbers of backslashes, and this is the result

KranthiPakala-MSFT 46,492 Microsoft Employee

@Mehrsa Thanks for the sample details. Is your requirement to access those ID, name, surname, age values? If that is the case then you can use an expression as below to access the value of those columns using set variable activity.

Update your lookup query as below using alias:

User's image

Lookup output:

{
	"firstRow": {
		"A": "{\"id\":2, \"info\":{\"name\":\"John\", \"surname\":\"Smith\"}, \"age\":25}"
	},
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US 2)",
	"billingReference": {
		"activityType": "PipelineActivity",
		"billableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		],
		"totalBillableDuration": [
			{
				"meterType": "AzureIR",
				"duration": 0.016666666666666666,
				"unit": "Hours"
			}
		]
	},
	"durationInQueue": {
		"integrationRuntimeQueue": 0
	}
}

Set variable expression to capture ID value from lookup output.

@string(json(activity('Lookup1').output.firstRow.A).id)

User's image

Share via

Azure Data Factory set variable add backslash before double quote.

Your answer