将非JSON数据存入JSON属性索引关联列时触发错误

问题根源 #

你遇到的[S0001][13609]错误，是因为SQL Server查询优化器处理IIF表达式时，可能会提前计算JSON_VALUE分支——哪怕ISJSON(DETAILS_JSON)返回false，优化器也不会严格遵循IIF的条件判断顺序，直接对非JSON格式的DETAILS_JSON调用JSON_VALUE，触发JSON格式校验错误。

解决方案 #

根据你的SQL Server版本，选择以下修复方案：

方案1：使用TRY_JSON_VALUE（SQL Server 2017及以上版本）

TRY_JSON_VALUE是专门为处理非JSON输入设计的函数，输入不合法时会直接返回NULL，不会抛出格式错误。替换原计算列定义即可：

-- 先删除现有索引和计算列
DROP INDEX IF EXISTS IDX_FOO_BAR_ID ON foo
ALTER TABLE foo DROP COLUMN IF EXISTS vBar

-- 创建新的计算列并建立索引
ALTER TABLE foo ADD vBar AS TRY_JSON_VALUE(DETAILS_JSON, '$.bar')
CREATE INDEX IDX_FOO_BAR_ID ON foo (vBar)

方案2：使用CASE表达式（SQL Server 2016版本）

如果你的版本不支持TRY_JSON_VALUE，改用CASE表达式即可。相比IIF，优化器会严格遵循CASE的条件判断顺序，只有当ISJSON验证通过时才会执行JSON_VALUE：

-- 删除现有索引和计算列
DROP INDEX IF EXISTS IDX_FOO_BAR_ID ON foo
ALTER TABLE foo DROP COLUMN IF EXISTS vBar

-- 创建带CASE逻辑的计算列（若仍报错，可添加PERSISTED关键字强制存储计算值）
ALTER TABLE foo ADD vBar AS 
    CASE WHEN ISJSON(DETAILS_JSON) = 1 
         THEN JSON_VALUE(DETAILS_JSON, '$.bar') 
         ELSE NULL 
    END
CREATE INDEX IDX_FOO_BAR_ID ON foo (vBar)

官方示例正常运行的原因 #

官方文档中的示例大概率使用了TRY_JSON_VALUE或CASE表达式，而非IIF，从而规避了优化器提前求值的问题。

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