If you are processing JSON data and the JSON data is not a valid JSON below error will be occurred.
Msg 13609, Level 16, State 7, Line 13
JSON text is not properly formatted. Unexpected character is found at position.
Be sure that the JSON data is correct. You can check whether its correct or not using ISJSON function. You can find detailed information about JSON functions in the article named “JSON Functions in SQL Server(TSQL)”
For Example execute the below script. As you can see an error will be occured.
1 2 3 4 5 6 7 8 9 10 |
DECLARE @json_data NVARCHAR(4000) SET @json_data=N'{ "Person": { "Name": "Nurullah CAKIR", "Friends": ["Faruk ERDEM", "Hakan GURBASLAR", "Ogun OZALP"] }' SET @json_data=JSON_MODIFY(@json_data,'$.Person.Name','Faruk ERDEM') print @json_data |
If you change the JSON data as below, the problem will be corrected.
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @json_data NVARCHAR(4000) SET @json_data=N'{ "Person": { "Name": "Nurullah CAKIR", "Friends": ["Faruk ERDEM", "Hakan GURBASLAR", "Ogun OZALP"] } }' SET @json_data=JSON_MODIFY(@json_data,'$.Person.Name','Faruk ERDEM') print @json_data |
You can find online json formatter to check your json is valid on the web.
In addition you can find usefult json examples on article JSON Comment Tutorial with Examples