Storing JSON in Azure SQL Table

Question

How can I store JSON responses received from a web activity within Azure Data Factory in an Azure SQL table? The JSON output contains special characters that need to be preserved in the table.

I have tried passing the JSON as a string to the stored procedure and used an openjson function within it. But I had to use a replace function to replace the backslash with a blank. In that case, it takes the value as ""desc1"". So it's throwing an error saying "JSON not formatted at d".

For example, I have a JSON response as follows:

[
    {
        "name": "abc",
        "description": "\"desc1\""
    },
    {
        "name": "def",
        "description": "desc2"
    }
]

I need to store this data in the following format in the Azure SQL table:

name,description

abc,"desc1"

def,desc2

Can someone provide me with guidance on how to achieve this?

Thanks,

• Deepthika

Answer 1

Hello Sodi, Deepthika,

Welcome to the Microsoft Q&A forum.

To store JSON responses received from a web activity in an Azure SQL table while preserving special characters, make sure that the JSON response is passed as a string to the stored procedure.

You can use the REPLACE function to replace backslashes with double backslashes before using OPENJSON.

CREATE PROCEDURE StoreJsonData
    @json NVARCHAR(MAX)
AS
BEGIN
    SET @json = REPLACE(@json, '\', '\\')
    INSERT INTO YourTable (name, description)
    SELECT name, description
    FROM OPENJSON(@json)
    WITH (
        name NVARCHAR(50),
        description NVARCHAR(255)
    )
END

and call the stored procedure and pass the JSON string as a parameter.

I hope this helps.