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

Question

I am new to JSON in SQL. I am getting the error "JSON text is not properly formatted. Unexpected character 'N' is found at position 0." while executing the below -

DECLARE @json1 NVARCHAR(4000)
set @json1 = N'{"name":[{"FirstName":"John","LastName":"Doe"}], "age":31, "city":"New York"}'
DECLARE @v NVARCHAR(4000)
set @v = CONCAT('N''',(SELECT value FROM OPENJSON(@json1, '$.name')),'''')
--select @v as 'v'
SELECT  JSON_VALUE(@v,'$.FirstName')

the " select @v as 'v' " gives me

N'{"FirstName":"John","LastName":"Doe"}'

But, using it in the last select statement gives me error.

DECLARE @v1 NVARCHAR(4000)
set @v1 = N'{"FirstName":"John","LastName":"Doe"}'
SELECT  JSON_VALUE(@v1,'$.FirstName') as 'FirstName'

also works fine.

Concent

You don't appear to understand what that N is doing when you put it in front of a string. It is not meant to be an actual part of the string value, it simply means to convert the string to a NVARCHAR value instead of a VARCHAR. – Tautomerism

i am getting this ..JSON text is not properly formatted. Unexpected character '1' is found at position 0. any idea whats wrong.. – Almagest

Answer 1

You are adding the Ncharacter in your CONCAT statement.

Try changing the line:

set @v = CONCAT('N''',(SELECT value FROM OPENJSON(@json1, '$.name')),'''')

to:

set @v = CONCAT('''',(SELECT value FROM OPENJSON(@json1, '$.name')),'''')

Hathaway

changing to set @v =(SELECT value FROM OPENJSON(@json1, '$.name')) did the trick – Concent

Answer 2

If you're using SQL Server 2016 or later there is build-in function ISJSON which validates that the string in the column is valid json.

Therefore you can do things like this:

SELECT 
  Name,
  JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode
FROM People
WHERE ISJSON(jsonCol) > 0

Lutz

Answer 3

JSON_VALUE function may first be executed on all rows before applying the where clauses. It will depend on execution plan, it means that small things like having top x clause or order by may have a impact on that.

• It means that if your json data is invalid anywhere in that column(in the whole table), it will throw an error when the query is executed.
• So find and fix those invalid json formats first. For example if that column has a ' instead of " in the Json payload it cannot be parsed and will cause the whole TSQL query to throw an error
• you can play with top 10 and order by id to make the result set smaller and find the row(s) which cause the json parser to break

Allwein