T-SQL中ISJSON在CTE中为何无法过滤无效JSON数据？

这不是bug，是SQL Server查询优化器的执行计划顺序问题导致的。

当你把ISJSON(JsonField)=1放在CTE内部时，优化器会先过滤掉无效JSON的记录，再执行后续的JSON函数操作。但如果在外层加LEN(JsonData)>500这类过滤条件，优化器可能会调整执行顺序——先执行LEN过滤（它认为这个操作成本更低），再去执行JSON_QUERY，此时无效JSON的记录还没被过滤掉，自然就触发了解析错误。

给你几个可行的解决办法：

• 将所有过滤条件整合到CTE/子查询内部：确保ISJSON校验和其他过滤逻辑同时执行，先筛掉无效数据再处理JSON

  WITH ValidJsonCTE AS (
      SELECT JsonField
      FROM MyTable
      WHERE ISJSON(JsonField) = 1 
        AND LEN(JsonField) > 500 -- 把外层过滤移到这里
  )
  SELECT JSON_QUERY(JsonField, '$.SomeProperty')
  FROM ValidJsonCTE
  

• 用CROSS APPLY强制执行顺序：先通过CASE判断生成有效JSON的列，再在外层做过滤，确保JSON函数只处理校验后的有效数据

  SELECT JSON_QUERY(v.ValidJson, '$.SomeProperty')
  FROM MyTable
  CROSS APPLY (
      SELECT CASE WHEN ISJSON(JsonField) = 1 THEN JsonField ELSE NULL END AS ValidJson
  ) v
  WHERE LEN(v.ValidJson) > 500
  

• 继续使用临时表：临时表会物化过滤后的结果，相当于强制固定了执行顺序，无效JSON已经被筛掉，后续操作不会再触发解析错误，这也是你当前用的临时方案，稳定性很高

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