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

Question

Many have this problem, however it's usually because the JSON is incorrect. Can anyone tell me why this does not work. I expect to get a result set of:

ONE  FirstName LastName
1    John      Smith

Instead, I get an empty result set and the following error message in SSMS:

Msg 13609, Level 16, State 2, Line 20
JSON text is not properly formatted. Unexpected character 'J' is found at position 0.

DECLARE @IN_JSON NVARCHAR(MAX) = 
N'{
    "resume": {
    "mailAddress": {
      "sectionOrder": 10,
      "section": {
        "firstName": "John",
        "lastName": "Smith"
      }
    }
  }
}'


 
SELECT '1' AS [One],
       JSON_VALUE(a.value, '$.firstName') AS [FirstName],
       JSON_VALUE(a.value, '$.lastName') AS [LastName]
  FROM OPENJSON (@IN_JSON, '$.resume.mailAddress.section') AS a

score 0 · Answer 1 · answered Jul 26 '22 at 08:02

0

Since you're trying to apply the JSON_VALUE() function to a non-JSON value, that's wrong. Indeed, you already can extract the desired column values by using OPENJSON(), just apply a conditional aggregation for this case such as

SELECT '1' AS [One],
       MAX(CASE WHEN [key] = 'firstName' THEN value END) AS [FirstName],
       MAX(CASE WHEN [key] = 'lastName' THEN value END) AS [LastName]
  FROM OPENJSON (@IN_JSON, '$.resume.mailAddress.section') AS a

Demo

answered Jul 26 '22 at 08:02

Barbaros Özhan

59,113
10
31
55

Thank You! A fellow colleague showed me another way as well. – Louis Loglisci Jul 26 '22 at 20:18
you're welcome @LouisLoglisci good luck ! – Barbaros Özhan Jul 26 '22 at 20:20

score 0 · Answer 2 · answered Jul 26 '22 at 20:30

   -- A colleague showed me the following  which works as well. And thank you again Barbaros for the very fast response.

DECLARE @IN_JSON NVARCHAR(MAX) = 
    N'{
        "resume": {
        "mailAddress": {
          "sectionOrder": 10,
          "section": {
            "firstName": "John",
            "lastName": "Smith"
          }
        }
      }
    }'
    
    SELECT '1' AS [One]
        , JSON_VALUE(@IN_JSON, '$.resume.mailAddress.section.firstName') AS [FirstName]
        , JSON_VALUE(@IN_JSON, '$.resume.mailAddress.section.lastName') AS [LastName]

-- Output result set:
-- One  FirstName   LastName
-- 1    John        Smith

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jul 28 '22 at 01:37

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

2 Answers2