SQL Server执行增删查时JSON格式错误原因及排查求助

1. 三条语句报错的根本原因 #

报错并非来自UserPreferences列，而是表中的计算列OtherLanguages：

• 该列定义为json_query([CustomFields],N'$.OtherLanguages')，依赖CustomFields列的JSON解析逻辑
• 你插入数据时给CustomFields赋值为空字符串''，而非NULL。空字符串不属于合法JSON格式，JSON_QUERY解析时会直接抛出"JSON格式不正确"的错误
• SELECT *会自动包含所有计算列，触发JSON_QUERY执行；UPDATE/DELETE操作因系统版本表（SYSTEM_VERSIONING = ON）的维护逻辑，需要计算该列的值，因此同样触发报错
• 你之前仅用ISJSON检查了UserPreferences，未验证CustomFields，因此误以为JSON相关列无问题

2. 快速定位问题列的方法 #

无需逐个查询列，可通过以下方式快速定位：

• 排除法批量测试：一次性查询所有非计算列，若能正常返回，则问题必然出在计算列。示例：

-- 查询所有非计算列，若正常则问题在计算列
SELECT PersonID, FullName, PreferredName, IsPermittedToLogon, LogonName, IsExternalLogonProvider, HashedPassword, IsSystemUser, IsEmployee, IsSalesperson, UserPreferences, PhoneNumber, FaxNumber, EmailAddress, Photo, CustomFields, LastEditedBy, ValidFrom, ValidTo
FROM Application.People WHERE PersonID = 1;

• 直接检查计算列定义：通过系统视图查询表中的计算列及其表达式，快速定位涉及JSON函数的列：

SELECT name AS 计算列名, definition AS 计算表达式
FROM sys.computed_columns
WHERE object_id = OBJECT_ID('Application.People');

• 用TRY_JSON验证可疑列：对所有可能涉及JSON的列（包括计算列依赖的源列）进行合法性检查：

SELECT 
  PersonID,
  ISJSON(CustomFields) AS CustomFields_IsValidJSON,
  TRY_JSON(CustomFields) AS CustomFields_TryParse
FROM Application.People WHERE PersonID = 1;

验证与修复示例 #

执行以下SQL可验证问题并修复：

-- 验证空字符串的JSON合法性（返回0，说明不是合法JSON）
SELECT ISJSON('') AS EmptyString_IsValidJSON;

-- 修复数据：将CustomFields设为NULL而非空字符串
UPDATE Application.People 
SET CustomFields = NULL 
WHERE PersonID = 1;

-- 此时再执行SELECT/UPDATE/DELETE即可正常运行
SELECT * FROM Application.People WHERE PersonID = 1;

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