使用OPENJSON解析JSON字符串报错，但JSON经校验合法

常见原因及修复方案 #

• 变量类型与字符串转义问题
  确保@json是NVARCHAR(MAX)类型（而非VARCHAR），避免编码差异导致的非法字符识别错误。如果用单引号包裹JSON字符串赋值，内部的单引号需转义为两个单引号，双引号直接保留即可。示例：

  DECLARE @json NVARCHAR(MAX) = '{"created": "2024-01-01", "desc": "It''s valid"}';
  

• 不可见控制字符干扰
  错误提示位置13存在意外字符，大概率是JSON中混入了不可见的Unicode控制字符（如换行、制表符、UTF-8 BOM）。用以下语句定位该字符的Unicode值：

  DECLARE @pos INT = 13;
  SELECT 
      SUBSTRING(@json, @pos, 1) AS TargetChar,
      UNICODE(SUBSTRING(@json, @pos, 1)) AS UnicodeCode;
  

  如果返回的UnicodeCode是65279（BOM）、9（制表符）、10（换行）或13（回车），执行以下语句清理：

  SET @json = TRIM(CHAR(0) + CHAR(9) + CHAR(10) + CHAR(13) + CHAR(65279) FROM @json);
  

• SQL Server严格的JSON语法校验
  部分在通用校验工具中合法的语法，SQL Server不支持：

  • 禁止键名用单引号，必须用双引号（如{'created': '2024-01-01'}不合法）
  • 禁止对象/数组末尾加多余逗号（如{"created": "2024-01-01",}不合法）
  • 字符串内的双引号必须转义（如{"desc": "He said \"hello\""}，SQL变量中需写为{"desc": "He said ""hello"""}）

测试修复后的查询 #

清理完成后，用以下语句提取created字段：

-- 单个JSON对象
SELECT JSON_VALUE(@json, '$.created') AS created;

-- JSON数组
SELECT created
FROM OPENJSON(@json)
WITH (created DATE '$.created');

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