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

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

CodePudding user response：

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

CodePudding user response：

   -- 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