CODEWITHSUNDEEP
azureazure-devopsazure-sql-databaseazure-data-factory
Dinesh Madhup
Dinesh Madhup

Reputation: 367

How to remove escape character from the output of Lookup activity in Azure Data Factory?

I am reading JSON data from SQL Database in Azure Data Factory. I have Azure Data Factory (ADF) pipeline, contains "Lookup" activity, which reads the JSON Data from SQL DB and bring into ADF Pipeline. Somehow the escape character (" \ ") get inserted in JSON data when I see at the output of Lookup activity of ADF.

For Example, the output of Lookup activity become like this: {\ "resourceType\ ":\ "Sales","id" :\ "9i5W6tp-JTd-24252\ "

Any idea how to remove the escape character from JSON in pipeline?

Update:

Thanks for the update Joseph. When I try your steps, It doesn't work for me.

  1. In lookup am reading data from SQL DB.

enter image description here

  1. This is my Append variable:

enter image description here

After running it, I still see escape character

{
    "firstRow": {
        "JSONData": "{\"resourceType\":\"counter\",\"id\":\"9i5W6tp-JTd- and more

Upvotes: 4

Views: 6545

Answers (1)

Joseph Xu
Joseph Xu

Reputation: 6063

As we know, '\' is an escape character. In your case, this symbol appears because it is used to escape one double quote inside a pair of double quotes.
For example, "\"" => """.

But it doesn't matter, we only need to convert it from string type to json type, it will automatically remove escape characters. I've created a test to verify it.

  1. First, I defined an Array type variable. enter image description here
  2. My Lookup activity's output is as follows: enter image description here
  3. Then I used an AppendVariable activity and used an expression @json(activity('Lookup1').output.firstRow.value) to convert it from string type to json type.
    enter image description here
  4. After I run debug, we can see the result as follows, there is no '\'. enter image description here

Upvotes: 2

Related Questions