Kusto查询：mv-expand后如何生成含拆分与原指标的目标表？

原始数据表 #

let tbl = datatable (metric:string, ct:int, details:dynamic)
[ 
    "Some1",5, dynamic([
        {
        "Key": "NO",
        "Value": 4
        },
        {
        "Key": "CA",
        "Value": 1
        }
    ]),
    "Some2",10, dynamic([
        {
        "Key": "GB",
        "Value": 10
        }])
];

原始数据展示：

metric | ct  | details
-------------------------------------------------------------------------
Some1  | 5   | [{"Key":"NO","Value":4},{"Key":"CA","Value":1}]
Some2  | 10  | [{"Key":"GB","Value":10}]

目标输出表 #

metric   | ct  
 ----------------
 Some1-NO | 4   
 Some1-CA | 1
 Some1    | 5
 Some2-GB | 10
 Some2    | 10

已完成的中间步骤 #

通过mv-expand展开details集合后，得到中间表：

metric | ct | details
-----------------------------------------
Some1  | 5 | {"Key":"NO","Value":4}
Some1  | 5 | {"Key":"CA","Value":1}
Some2  | 10 | {"Key":"GB","Value":10}

当前查询语句：

tbl
| mv-expand details
| ??

后续实现步骤 #

完整查询语句如下：

let tbl = datatable (metric:string, ct:int, details:dynamic)
[ 
    "Some1",5, dynamic([
        {
        "Key": "NO",
        "Value": 4
        },
        {
        "Key": "CA",
        "Value": 1
        }
    ]),
    "Some2",10, dynamic([
        {
        "Key": "GB",
        "Value": 10
        }])
];
// 生成带Key后缀的明细行
let detail_rows = tbl
| mv-expand details
| extend combined_metric = strcat(metric, "-", details.Key)
| project metric = combined_metric, ct = details.Value;
// 合并明细行与原始汇总行
union detail_rows, (tbl | project metric, ct)
// 按metric排序，匹配目标表顺序
| sort by metric, ct desc

步骤说明： #

• 提取明细字段：用extend从展开的details中提取Key，拼接成新的metric名称；同时提取Value作为对应行的ct值
• 整理列结构：用project将拼接后的名称和值映射为目标表的列名
• 合并数据：用union把明细行与原始表的汇总行（仅保留metric和ct）合并
• 排序（可选）：用sort让结果顺序与目标表一致

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