Remove BackSlash in result of Lookup Activity.

Muthu Kugan Muthusivakumar (INFOSYS LIMITED) 0 Reputation points Microsoft Vendor
2024-05-01T16:36:06.22+00:00

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.

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,853 questions
{count} votes

1 answer

Sort by: Most helpful
  1. phemanth 11,285 Reputation points Microsoft Vendor
    2024-05-02T09:47:53.7933333+00:00

    @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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.