SQL Server 2017更新损坏JSON列报文本格式错误如何处理

SQL Server 所有内置JSON函数（包括JSON_MODIFY）的前置要求是输入的JSON文本格式完全合法，只要存在格式错误（比如缺失双引号、括号不匹配等）就会直接抛出"JSON text is not properly formatted"报错，没有直接绕过JSON校验调用JSON_MODIFY的参数，可按以下步骤处理：

步骤1：优先处理格式正常的JSON条目 #

先用ISJSON函数筛选出格式合法的行，先完成这部分的更新操作，避免影响正常数据：

UPDATE 你的表名
SET json_col = JSON_MODIFY(json_col , '$.date', NULL)
WHERE ISJSON(json_col) = 1

步骤2：处理格式损坏的JSON条目 #

这部分数据无法使用JSON函数操作，只能通过字符串匹配的方式移除date字段，你需要先统计损坏JSON的通用格式规律（比如缺失双引号的date键是date:还是其他形式），再用字符串函数做针对性替换，示例如下：

UPDATE 你的表名
SET json_col = 
  -- 移除带双引号的date键值对，同时处理前后逗号避免残留语法错误
  REPLACE(
    REPLACE(
      REPLACE(json_col, ',"date":null', ''),
      '"date":null,', ''
    ),
    '"date":null', ''
  )
  -- 移除缺失双引号的date键值对，可根据实际格式调整匹配规则
  ,REPLACE(
    REPLACE(
      REPLACE(json_col, ',date:', ''),
      'date:,', ''
    ),
    'date:', ''
  )
WHERE ISJSON(json_col) = 0
AND json_col LIKE '%date%' -- 仅筛选包含date字段的行，减少误改风险

注意事项 #

• 字符串替换操作前建议先执行SELECT验证替换逻辑符合预期，避免误改其他字段内容
• 如果后续需要这部分损坏的JSON可以正常使用JSON函数，可先针对常见格式问题做批量修复（比如给未加双引号的键补双引号），再调用JSON函数操作

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