如何用KQL解析Relations_s字段JSON提取Azure DevOps工作项关联数据

核心解析逻辑：先将字符串格式的JSON数组Relations_s转为动态类型数组，拆解单条关联记录后，通过正则从关联URL末尾提取工作项数字ID，同时映射系统内置关联类型为可读名称，即可按需输出长表、宽表两种结构。

长表格式（每行对应单条关联关系） #

该格式适合做关联关系明细统计、工作项链路追溯，每条记录对应一个源工作项和一个关联工作项的绑定关系：

azure_devops_work_item_events_CL
| summarize any(WorkItemType_s, TeamProject_s, Title_s, 
      AssignedTo_s, State_s, Reason_s) by WorkItemId_d, Relations_s
// 解析JSON数组并拆解每条关联记录
| extend RelationsArr = parse_json(Relations_s)
| mv-expand Relation = RelationsArr to typeof(dynamic)
| where isnotempty(Relation)
// 映射系统关联类型为可读名称
| extend LinkTypeRaw = tostring(Relation.rel)
| extend LinkType = case(
    LinkTypeRaw == "System.LinkTypes.Hierarchy-Reverse", "Parent",
    LinkTypeRaw == "System.LinkTypes.Hierarchy-Forward", "Child",
    LinkTypeRaw == "System.LinkTypes.Related", "Related",
    split(LinkTypeRaw, "/")[-1]
)
// 从URL末尾提取关联工作项数字ID
| extend RelatedWorkItemId = tolong(extract(@".*/workitems/(\d+)$", 1, tostring(Relation.url)))
| where isnotempty(RelatedWorkItemId)
// 选择输出字段
| project WorkItemId_d, WorkItemType_s, TeamProject_s, Title_s, AssignedTo_s, State_s, Reason_s, LinkType, RelatedWorkItemId
| order by WorkItemId_d desc, RelatedWorkItemId

宽表格式（按关联类型分列展示） #

该格式适合日常工作项清单查看，同类型关联ID聚合为数组列，按Parent/Child/Related等类型分字段存储：

azure_devops_work_item_events_CL
| summarize any(WorkItemType_s, TeamProject_s, Title_s, 
      AssignedTo_s, State_s, Reason_s) by WorkItemId_d, Relations_s
| extend RelationsArr = parse_json(Relations_s)
| mv-expand Relation = RelationsArr to typeof(dynamic)
| where isnotempty(Relation)
| extend LinkTypeRaw = tostring(Relation.rel)
| extend LinkType = case(
    LinkTypeRaw == "System.LinkTypes.Hierarchy-Reverse", "Parent",
    LinkTypeRaw == "System.LinkTypes.Hierarchy-Forward", "Child",
    LinkTypeRaw == "System.LinkTypes.Related", "Related",
    split(LinkTypeRaw, "/")[-1]
)
| extend RelatedWorkItemId = tolong(extract(@".*/workitems/(\d+)$", 1, tostring(Relation.url)))
| where isnotempty(RelatedWorkItemId)
// 按关联类型聚合为独立列
| summarize 
    ParentWorkItemIds = make_set_if(RelatedWorkItemId, LinkType == "Parent"),
    ChildWorkItemIds = make_set_if(RelatedWorkItemId, LinkType == "Child"),
    RelatedWorkItemIds = make_set_if(RelatedWorkItemId, LinkType == "Related"),
    any(WorkItemType_s, TeamProject_s, Title_s, AssignedTo_s, State_s, Reason_s)
    by WorkItemId_d
| order by WorkItemId_d desc

注意事项 #

• 若Relations_s的JSON结构中关联类型、URL的字段名和示例逻辑不匹配，可先执行azure_devops_work_item_events_CL | project Relations_s | take 1查看单条原始JSON结构，对应调整字段取值逻辑即可。
• 示例中的ID提取正则适配所有Azure DevOps标准工作项关联URL，不受组织名、项目名路径长度影响，只要URL末尾为/workitems/<数字ID>格式即可准确提取。
• 宽表默认将同类型关联ID存为数组列，若需要拆为独立标量列（比如单Parent场景取唯一父ID、固定取前N个关联子项），可在聚合后通过extend ParentWorkItemId = ParentWorkItemIds[0]这类索引取值逻辑按需调整。

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