0
votes

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
1
So you want to convert {"resourceType":"Sales","id":"9i5W6tp-JTd-24252"} to {"resourceType":Sales,"id":9i5W6tp-JTd-24252},like this? - Joseph Xu
Actually the output of lookup activity is like this: "{\ "resourceType\":\ "Sales\",\ "id\ ":\ "9i5W6tp-JTd-24252\ "} And I want to remove all escape character and output should be like this: {"resourceType":"Sales","id":"9i5W6tp-JTd-24252"} - Dinesh Madhup

1 Answers

1
votes

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