Parsing JSON column in SQL Server Table - JSON text is not properly formatted. Unexpected character '.' is found at position 0.

Question

Dear Team,

Below is my one of JSON Column Name is "VARIALBLES" from the SQL Server table,

{"EmbedContextParameters":null,"PipeData":{"First Name":"Rajkumar","Last Name":"Govindaraj","ID":"","FormType":"LeadArtist","ESubDept":"","Programme":"Kusama","ProjectCode":"AX-1234","RoleList":"","locale":"en-us"}}

Remaining colunms Email, Gender, etc,. as normal.

And, I'm trying to explode new columns from the one of Json column in a Sql Server table.

Below is my code that i tried and keep getting same error msg like in below,

---

Msg 13609, Level 16, State 4, Line 76

JSON text is not properly formatted. Unexpected character '.' is found at position 0.

---

SELECT * FROM forms.EDI
CROSS APPLY OPENJSON(VARIABLES) 
WITH(
       FirstName NVARCHAR(MAX),
       LastName NVARCHAR(MAX),
	   ID INT,
	   FormType NVARCHAR(MAX),
	   ESubDept NVARCHAR(MAX),
	   Programme NVARCHAR(MAX),
	   ProjectCode NVARCHAR(MAX),
	   RoleList NVARCHAR(MAX),
	   locale NVARCHAR(MAX)
	   ) as my_json

Please help me someone to solve this problem.

Answer 1

To find the problematic values, try this query:

select *
from forms.EDI
where isjson(VARIABLES) = 0

The shown JSON seems valid, however the WITH part can be adjusted like in this example:

declare @EDI table (VARIABLES nvarchar(max))
insert @EDI values 
(N'{"EmbedContextParameters":null,"PipeData":{"First Name":"Rajkumar","Last Name":"Govindaraj",
"ID":"","FormType":"LeadArtist","ESubDept":"","Programme":"Kusama","ProjectCode":"AX-1234",
"RoleList":"","locale":"en-us"}}')

SELECT * FROM @EDI
CROSS APPLY OPENJSON(VARIABLES) 
WITH(
       FirstName NVARCHAR(MAX) '$.PipeData."First Name"',
       LastName NVARCHAR(MAX) '$.PipeData."Last Name"',
	   ID INT '$.PipeData.ID',
	   FormType NVARCHAR(MAX) '$.PipeData.FormType',
	   ESubDept NVARCHAR(MAX) '$.PipeData.ESubDept',
	   Programme NVARCHAR(MAX) '$.PipeData.Programme',
	   ProjectCode NVARCHAR(MAX) '$.PipeData.ProjectCode',
	   RoleList NVARCHAR(MAX) '$.PipeData.RoleList',
	   locale NVARCHAR(MAX) '$.PipeData.locale'
	   ) as my_json

It can be also improved.

Answer 2

Your JSON data looks good, so far, but it's a complex data type and the column names don't match the attribute names.

This query works, so there could be something else wrong with the source data.

DECLARE @json NVARCHAR(2048) = N'
{"EmbedContextParameters" : null,
 "PipeData":{"First Name" : "Rajkumar",
             "Last Name" : "Govindaraj",
			 "ID" : "",
			 "FormType" : "LeadArtist",
			 "ESubDept" : "",
			 "Programme" : "Kusama",
			 "ProjectCode" : "AX-1234",
			 "RoleList" : "",
			 "locale" : "en-us"}}
}';

SELECT * 
FROM OpenJson(@json, N'$.PipeData') WITH(
       [First Name] NVARCHAR(MAX),
       [Last Name] NVARCHAR(MAX),
	   ID INT,
	   FormType NVARCHAR(MAX),
	   ESubDept NVARCHAR(MAX),
	   Programme NVARCHAR(MAX),
	   ProjectCode NVARCHAR(MAX),
	   RoleList NVARCHAR(MAX),
	   locale NVARCHAR(MAX)
	   ) as my_json