SQL Server递归CTE解析JSON：锚点与递归部分类型不匹配如何解决？

问题背景 #

在SQL Server 2022数据库中，将现有函数重写为内联表值函数时，使用递归CTE解析JSON遇到**“锚点与递归部分类型不匹配”**的错误。尝试为OPENJSON返回的Key列添加显式CAST转换后问题仍未解决，移除该列后查询可正常执行。

错误原因 #

• 递归CTE的核心约束是：锚点成员与递归成员的对应列必须数据类型、长度、排序规则完全一致
• OPENJSON返回的[Key]列默认使用数据库的默认排序规则，而锚点成员中定义的ThePath列排序规则可能与前者不一致。即使显式转换了数据类型，排序规则的差异仍会被SQL Server判定为“类型不匹配”，触发错误

修复方案 #

• 在递归成员中对v.[Key]进行CAST转换时，同时显式指定与锚点成员ThePath列一致的排序规则，例如使用COLLATE DATABASE_DEFAULT匹配数据库默认排序规则
• 确保锚点与递归成员的对应列在类型、长度、排序规则上完全对齐，消除SQL Server的类型校验冲突

修复后的代码示例 #

DECLARE @json NVARCHAR(MAX) =N'{
    "menu": {  
        "id": "file",  
        "value": "File",  
        "popup": {  
            "menuitem": [  
                {"value": "New", "onclick": "CreateDoc()"},
                {"value": "Open", "onclick": "OpenDoc()"},
                {"value": "Save", "onclick": "SaveDoc()"}  
            ]  
        }  
    }
}';

WITH cteRecurseJSON AS
(
    -- 锚点成员
    SELECT 
        CAST(1 AS INTEGER) AS Depth, 
        CAST(N'' AS NVARCHAR(4000)) AS ThePath,
        CAST(@json AS NVARCHAR(MAX)) AS TheValue, 
        CAST('object' AS VARCHAR(10)) AS ValueType
    UNION ALL
    -- 递归成员：显式指定排序规则匹配锚点成员
    SELECT 
        r.Depth + 1 AS Depth,
        CAST(v.[Key] AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS ThePath,
        COALESCE(Value, '') AS TheValue,
        CAST(
            CASE Type 
                WHEN 1 THEN 'string'
                WHEN 0 THEN 'null'
                WHEN 2 THEN 'int'
                WHEN 3 THEN 'boolean'
                WHEN 4 THEN 'array' 
                ELSE 'object' 
            END AS VARCHAR(10)
        ) AS ValueType
    FROM cteRecurseJSON r
    CROSS APPLY OPENJSON(r.TheValue) v
    WHERE r.ValueType IN ('array','object')
)
SELECT * FROM cteRecurseJSON;

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