如何修改dbo.JSONHierarchy函数添加KeyPath与JSONdata_Used字段

针对你的问题，核心是要在递归解析JSON的过程中正确传递父节点路径并保留当前节点的原始JSON片段，以下是具体的修改方案：

1. 函数修改核心逻辑 #

原函数的递归逻辑仅处理了层级关系，但未传递路径和当前节点JSON。我们需要：

• 新增递归参数传递父节点的KeyPath和当前节点JSON
• 在递归每一步根据父节点类型（对象/数组）拼接当前节点的完整KeyPath
• 直接从OPENJSON结果中提取当前节点的JSON片段作为JSONdata_Used

2. 修改后的完整函数代码 #

CREATE OR ALTER FUNCTION dbo.JSONHierarchy
(
    @JSON NVARCHAR(MAX),
    @ParentElementID INT = NULL,
    @CurrentKeyPath NVARCHAR(MAX) = '$',
    @CurrentNodeJSON NVARCHAR(MAX) = NULL
)
RETURNS TABLE
AS
RETURN
WITH Hierarchy AS
(
    -- 锚点成员：处理根节点
    SELECT
        Element_ID = 1,
        Parent_ID = @ParentElementID,
        [Object] = CASE WHEN ISJSON(@JSON) = 1 THEN JSON_QUERY(@JSON) END,
        [Key] = NULL,
        [Value] = CASE WHEN ISJSON(@JSON) <> 1 THEN @JSON END,
        Type = CASE WHEN ISJSON(@JSON) = 1 THEN 1 ELSE 0 END,
        KeyPath = ISNULL(@CurrentKeyPath, '$'),
        JSONdata_Used = ISNULL(@CurrentNodeJSON, @JSON)
    WHERE ISJSON(@JSON) IN (0,1)

    UNION ALL

    -- 递归成员：处理子节点
    SELECT
        Element_ID = ROW_NUMBER() OVER (ORDER BY (SELECT 1)) + (SELECT MAX(Element_ID) FROM Hierarchy),
        Parent_ID = h.Element_ID,
        [Object] = CASE WHEN j.[Type] IN (4,5) THEN j.[Value] END,
        [Key] = j.[Key],
        [Value] = CASE WHEN j.[Type] NOT IN (4,5) THEN j.[Value] END,
        Type = j.[Type],
        -- 根据父节点类型拼接KeyPath
        KeyPath = 
            CASE
                WHEN h.Type = 4 THEN CONCAT(h.KeyPath, '[', j.[Key], ']') -- 数组元素用[索引]格式
                ELSE CONCAT(h.KeyPath, '.', j.[Key]) -- 对象属性用.键名格式
            END,
        JSONdata_Used = j.[Value] -- 当前节点的原始JSON片段
    FROM Hierarchy h
    CROSS APPLY OPENJSON(h.[Object]) j
    WHERE h.Type IN (1,4) -- 仅递归处理对象或数组类型节点
)
SELECT * FROM Hierarchy;

3. 关键修改说明 #

• 参数新增：@CurrentKeyPath用于传递父节点的完整路径，@CurrentNodeJSON传递当前节点的原始JSON
• KeyPath拼接：
  • 父节点是数组（Type=4）时，路径格式为父路径[索引]，比如$.employees[0]
  • 父节点是对象（Type=1）时，路径格式为父路径.键名，比如$.employees[0].name
• JSONdata_Used：直接使用OPENJSON返回的Value字段，对于对象/数组类型，该字段就是对应节点的JSON字符串；对于基本类型，就是值本身

4. 测试示例 #

用多层嵌套的员工JSON测试：

DECLARE @EmployeeJSON NVARCHAR(MAX) = N'{
    "company": "Tech Corp",
    "employees": [
        {
            "id": 1,
            "name": "Alice",
            "department": {
                "name": "Engineering",
                "team": "Backend"
            }
        },
        {
            "id": 2,
            "name": "Bob",
            "department": {
                "name": "Marketing",
                "team": "Digital"
            }
        }
    ]
}';

SELECT Element_ID, Parent_ID, [Key], [Value], Type, KeyPath, JSONdata_Used
FROM dbo.JSONHierarchy(@EmployeeJSON);

执行后会正确输出所有层级的KeyPath和对应节点的JSON片段，比如$.employees[0].department对应的JSONdata_Used就是{"name": "Engineering", "team": "Backend"}

5. 常见问题排查 #

• 如果多层嵌套路径错误：检查递归时的KeyPath拼接逻辑，确保数组用[索引]而非.连接
• 如果JSONdata_Used为空：确认递归时是否正确传递了j.[Value]，以及父节点的[Object]是否为有效的JSON对象/数组

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