Data Factory: How to pass JSON as text to Web Body

Question

I have to pass multiple records from a SQL source to the body of a web call in Data Factory in JSON format.

eg.

{"EmployeeCode":"1234",""FirstName":"Joe","LastName":"Bloggs"}
{"EmployeeCode":"5678",""FirstName":"Jack","LastName":"Ryan"}

However, I cannot seem to do it without ADF adding escape characters "\".

{\\"EmployeeCode\\":\\"1234\\",\\"\\"FirstName\\":\\"Joe\\",\\"LastName\\":\\"Bloggs\\"}"

So far I have tried:

1. Copy activity - Formatted SQL as JSON
2. Copy activity - SQL output to BLOB JSON
3. Copy activity - SQL concatenated string output

Does anyone have a process by which to achieve this? I am hoping I have missed something easy.

Thanks in advance....

Answer 1

Escape character backslash \ appears wherever double quotes " are used in the string in the Azure data factory.

Thank you @GregGalloway for your valuable input in the comments sections. As @GregGalloway mentioned, convert the string to JSON format in the web body as shown in the below example.

Example:

1. Source: SQL data

2. Getting SQL records using lookup activity.

3. Passing the output record to web activity in JSON format.

@json(activity('Lookup1').output.value[0].description)

The input of the web activity body is shown as below in JSON format: