7

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.

user9057272
  • 367
  • 2
  • 5
  • 17
  • 2
    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. – dfundako Oct 12 '18 at 15:51
  • i am getting this ..JSON text is not properly formatted. Unexpected character '1' is found at position 0. any idea whats wrong.. – shashank Sep 26 '22 at 11:54

3 Answers3

13

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
osynavets
  • 1,199
  • 1
  • 12
  • 22
7

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')),'''')
Lauren Rutledge
  • 1,195
  • 5
  • 18
  • 27
Eric
  • 94
  • 4
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 first. for example if that column has a ' instead of " it cannot be parsed and will cause the whole TSQL query to throw an error
Iman
  • 17,932
  • 6
  • 80
  • 90