How to remove escape character at an JSON string array?

0
votes

In Azure Data Factory, I have Lookup activity. "Lookup" activity, which reads the JSON Data from SQL DB (more than 1 row) and bring into ADF Pipeline.

The lookup activity output contains escape character. Please see this:

 {\ "resourceType\ ":\ "counter","id" :\ "9i5W6tp-JTd-24252\ "

How to remove escape character? Any help is appreciated.

1
azureazure-devopsazure-data-factory
By the way, since the json function only supports string types instead of array types, we have to do more complicated work. - Joseph Xu

1 Answers

1
votes

Since your query result is an JSON String array, we need to do more to remove escape character.

Here is my steps:

  1. Firstly, we can define two array type variables. enter image description here

  2. Here is the summary, the Lookup activity will return an JSON String array. enter image description here Here is the output of my Lookup activity.The data structure will affect the following expression at the Append variable inside the Foreach activity. enter image description here

  3. At the ForEach activity we can use @activity('Lookup1').output.value to get the JSON String array. enter image description here

  4. Inside the ForEach activity, we can use @json(item().value) to get the one object of the JSON String array and remove escape character. enter image description here

  5. In the end, we can use a SetVariable activity to check the result. enter image description here

After I run debug. We can see the correct result:
enter image description here