Quotes and special character issue in set and append variable in ADF

Question

Hi , I'm trying to prepare a variable value using set and append variable that needs to be passed to the request body of the copy activity to fetch the data from API. I need to do lookup of 100 values from DB at a time and form a array of values but " (quote) value is getting appended in the starting, end and in between of values due to which the copy activity is failing. I'm trying for the first two values as of now.

"[\"{\\"RIC\\":{\\"Value\\":\\"FHN.N\\"}}\",\"{\\"RIC\\":{\\"Value\\":\\"0142.HK\\"}}\"]" - this is the final variable output that I'm getting, I've removed the additional \ (slashes) using @replace(string(variables('RIC')),'\','') and the output was - "[\"{\"RIC\":{\"Value\":\"FHN.N\"}}\",\"{\"RIC\":{\"Value\":\"0142.HK\"}}\"]" due to the " quotes in between the two RIC values and in between }] in the starting and at the end it's failing. If I can get the output as "[{\"RIC\":{\"Value\":\"FHN.N\"}},{\"RIC\":{\"Value\":\"0142.HK\"}}]" it would solve the issue as I tried passing this manually. Attaching the activity output screenshots and pipeline code as well.

Request body passed in copy activity - "{\"GetSignificantDevelopments_Request_1\":{\"FindRequest\":{\"CompanyIdentifiers_typehint\":[\"CompanyIdentifiers\",\"CompanyIdentifiers\"],\"CompanyIdentifiers\":[\"{\"RIC\":{\"Value\":\"FHN.N\"}}\",\"{\"RIC\":{\"Value\":\"0142.HK\"}}\"],\"StartDate\": \"2021-01-05T00:00:00\",\"EndDate\": \"2021-01-06T00:00:00\",\"Significance\": \"1 2 3\",\"MaxNumberOfItems\": 100}}}", - this is failing

manually tried successful request body - "{\"GetSignificantDevelopments_Request_1\":{\"FindRequest\":{\"CompanyIdentifiers_typehint\":[\"CompanyIdentifiers\",\"CompanyIdentifiers\"],\"CompanyIdentifiers\":[{\"RIC\":{\"Value\":\"FHN.N\"}},{\"RIC\":{\"Value\":\"0142.HK\"}}],\"StartDate\": \"2021-01-05T00:00:00\",\"EndDate\": \"2021-01-06T00:00:00\",\"Significance\": \"1 2 3\",\"MaxNumberOfItems\": 100}}}",

54102-pl-api-refinitiv-variable-test.txt

Answer 1

@Kunal Kumar Sinha Thanks for using Microsoft Q&A !!

After looking into your pipeline, I do not see any direct way to fix this other than replacing unwanted characters from the final string (RIC_LIST). Please use the below in your RIC_LIST set variable activity

@replace(replace(replace(replace(string(variables('RIC')),'\',''),'["{','[{'),'}"]','}]'),'}","{','},{')

This gives the below as final output which I believe you can pass to request body during your API call -
"value": "[{\"RIC\":{\"Value\":\"FHN.N\"}},{\"RIC\":{\"Value\":\"0142.HK\"}}]"

Please let me know if you have any other questions.

---

Please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community.