CODEWITHSUNDEEP
jsonsql-server
navid jamshidi
navid jamshidi

Reputation: 183

How to fix 'JSON text is not properly formatted. Unexpected character '\' is found at position 1.' error in SQL Server

I need to pass a JSON to stored procedure as a string and then convert that sting JSON using OPENJSON. The input parameter need be in string format and it contains \ scape characters.

The open JSON is available in SQL Server 2016, I have tried using OPENJSON without '\', it is perfectly working

This code is working:

DECLARE @test1 NVARCHAR(MAX)='{"name":"john","age":22,"class":"mca"}'
SELECT * FROM OPENJSON(@test1)

This is not working:

DECLARE @test2 NVARCHAR(MAX)='{\"name\":\"john smith\",\"age\":22,\"class\":\"mca\"}'
SELECT * FROM OPENJSON(@test2)

Upvotes: 3

Views: 17944

Answers (1)

Vitaly Borisov
Vitaly Borisov

Reputation: 1193

Not the best approach, however:

DECLARE @test2 NVARCHAR(MAX) = '{\"name\":\"john smith\",\"age\":22,\"class\":\"mca\"}';
SELECT j.* 
FROM OPENJSON('{"t":"' + @test2 + '"}') t
CROSS APPLY OPENJSON(t.[value]) j
;

Another solution:

SELECT *
FROM OPENJSON(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@test2,'\\t','\t'),'\\r','\r'),'\\n','\n'),'\\f','\f'),'\\b','\b'),'\"','"'),'\\','\')) t
;

Upvotes: 3

Related Questions