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.

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')),'''')

Answer 2

JSON_VALUE function may first be executed on all rows before applying the where clauses. it will depend on execution plan so small things like having top clause or ordering 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 (' instead of " , etc ) first without anywhere and then run your query.