Remove BackSlash in result of Lookup Activity.

Question

Hi All,

I am having an order table, I have written a simple query to convert it to my required schema and it works perfect in my SSMS.

Query:

select(select JSON_OBJECT(

'OrderNumber': OrderNumber

) as 'values'

from order

for Json Path

)as 'EventRequests'.

Result:

[

{"values":

{

"OrderNumber":77889947

}

}

].

But when i tried to execute the same query in Lookup of activity of Data factory, I am ending up with unnecessary backslashes r as below.

"EventRequests": "[{"values":{"OrderNumber":77889947}}]".

Please let me know how to remove these backslashes.

Answer 1

@Muthu Kugan Muthusivakumar (INFOSYS LIMITED)
Thanks for using MS Q&A platform and posting your query.

The backslashes are added to the result because the Lookup Activity returns the result as a string, and the backslashes are used to escape special characters in the string.

 use the replace function to remove the backslashes from the result. The replace function can be used to replace a substring in a string with another substring. In this case, the user can replace the backslashes with an empty string.

• Here's an example query that replaces the backslashes with an empty string:

select replace((select JSON_OBJECT(
'OrderNumber': OrderNumber
) as 'values'
from order
for Json Path
)as 'EventRequests', '\\', '') as 'EventRequests'

 The replace function replaces all occurrences of the substring in the string. In this case, it replaces all backslashes with an empty string.

• Test the modified query in the Lookup Activity to ensure that it produces the desired result.

Hope this helps. Do let us know if you any further queries.