SQL Server中JSON_QUERY用列值构造路径报错求助

错误原因 #

SQL Server 2016（13.0.4001属于该版本）对JSON函数的路径参数有严格限制：JSON_VALUE或JSON_QUERY的第二个参数必须是字符串字面量，不允许使用动态生成的表达式（比如你用CONCAT拼接的路径）作为参数，这是版本特性限制，和是否过滤空值、用CASE判断无关。

可行解决方案 #

针对你的需求，推荐用OPENJSON解析JSON数组，通过匹配数组索引的方式获取目标元素，避开动态路径的限制：

SELECT 
    CASE 
        WHEN sr.OptionId IS NOT NULL THEN oj.OptionContent
        ELSE NULL
    END AS SelectedOption
FROM Survey sr
OUTER APPLY (
    SELECT Value AS OptionContent
    FROM OPENJSON(sr.Options)
    -- 数组索引[key]是字符串类型，需将OptionId转为字符串匹配
    WHERE [key] = CAST(sr.OptionId AS VARCHAR(10))
) oj

方案说明 #

• OPENJSON会将JSON数组拆分为多行记录，其中[key]字段对应数组的索引（以字符串形式存储），通过将OptionId转为字符串后与[key]匹配，就能精准定位目标元素。
• 使用OUTER APPLY而非CROSS APPLY，确保当Options为空数组或OptionId为NULL时，查询仍能返回原表的所有行，符合你“为空时不关心返回值”的需求。
• 若使用SQL Server 2017及以上版本，可直接用FORMATMESSAGE生成动态路径（JSON_QUERY(sr.Options, FORMATMESSAGE('$[%d]', sr.OptionId))），但2016版本不支持该写法。

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