jsonsql-server-2016sql-server-json

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


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.


Solution

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