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.