SQL Server JSON格式错误排查：为何查询返回空结果并报13609错误？

问题描述 #

很多人都遇到过这类问题，通常是JSON格式不正确导致的。以下SQL代码无法正常运行，期望输出结果集：

ONE  FirstName LastName
1    John      Smith

但实际得到空结果集，同时在SSMS中收到错误提示：

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

对应的问题SQL代码：

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

问题原因 #

1. JSON格式非法：代码里用了HTML实体转义字符"代替标准双引号"，SQL Server的JSON解析器不认这种转义，直接判定JSON格式错误，触发13609报错。
2. OPENJSON用法错误：就算修复了转义问题，$.resume.mailAddress.section是一个JSON对象，OPENJSON解析这个路径时会把对象的每个键值对拆成单独行（也就是返回两行：一行是firstName对应John，一行是lastName对应Smith），此时a.value是单纯的字符串值，用JSON_VALUE(a.value, '$.firstName')根本没法从字符串里提取JSON属性，结果自然为空。

修复方案 #

方案一：直接用JSON_VALUE提取属性（无需OPENJSON） #

把JSON字符串里的"替换成标准双引号，直接通过JSON路径提取目标值：

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]

方案二：用OPENJSON配合WITH子句解析对象 #

如果一定要用OPENJSON，通过WITH子句定义要提取的属性和对应路径，直接解析整个JSON对象：

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

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

内容的提问来源于stack exchange，提问作者Louis Loglisci