Azure Data Factory Copy Activity convert sql string in to jobject

Question

I have a query from Azure SQL database which returns jsonstring like below and want to insert in to Cosmos db as JSON object (not string)

"{\"type\": \"Point\",\"coordinates\":[4.4468420323932429,45.154212148849346]}"

even though i use the JSON_Query the value is inserted as string in cosmos. Please suggest if there is any way to achieve this in ADF copy activity ( i cannot use Data flow Derived column parse method as Shared Integration runtime is used to connect to source and sink and Dataflow does not allow it)

Accepted Answer

First, create a stored procedure in your Azure SQL database that parses the JSON string and returns a JSON object :


CREATE PROCEDURE GetJsonObject

AS

BEGIN

    DECLARE @jsonString NVARCHAR(MAX)

    DECLARE @jsonObject NVARCHAR(MAX)

    -- Fetch your JSON string here, for example:

    SELECT @jsonString = '{"type": "Point","coordinates":[4.4468420323932429,45.154212148849346]}'

    -- Parse the JSON string

    SELECT @jsonObject = JSON_QUERY(@jsonString)

    -- Return the JSON object

    SELECT @jsonObject AS JsonObject

END

Then create a new pipeline and add a Copy Data Activity where you call the stored procedure :


EXEC GetJsonObject

Then set the sink dataset to your Cosmos DB. In the Mapping tab, ensure that the JSON object is correctly mapped.

Share via

Azure Data Factory Copy Activity convert sql string in to jobject

0 additional answers

Your answer