如何在Kusto中查询嵌套二维动态数组并筛选指定条件行？

问题背景 #

我有一个Kusto表，其中某列为dynamic类型的嵌套JSON，该动态对象是二维数组，示例结构如下：

{
    "OtherField": "Unknown",
    "First": [
        {
            "Id": "",
            "Second": [
                {
                    "ConfidenceLevel": "Low",
                    "Count": 3
                }
            ]
        },
        {
            "Id": "",
            "Second": [
                {                   
                    "ConfidenceLevel": "High",
                    "Count": 2
                },
                {
                    "ConfidenceLevel": "Low",
                    "Count": 2
                }
            ]
        }
    ]
}

需要筛选满足**ConfidenceLevel == 'High' 且 Count > 0**的行：只要二维数组中存在任意一个项符合条件，该行就应被选中。

此前使用tostring(ColumnName) has_cs '"Level":"High"'匹配Level为High的行，但现在需要更精确的条件；尝试过正则表达式tostring(ColumnName) matches regex '"Level":"High","Count":[^0]'，但评审禁止使用正则；尝试用mv-expand/mv-apply结合toscalar时，出现报错："The name 'ColumnName' does not refer to any column, table, varible or function."，错误代码示例如下：

let T = datatable(ColumnName:dynamic)
[
   dynamic({"OtherField": "Unknown","First": [{"Id": "","Second": [{"ConfidenceLevel": "Low","Count": 3}]},{"Id": "","Second":[{"ConfidenceLevel": "High","Count": 0}]}]}),
   dynamic({"OtherField": "Unknown","First": [{"Id": "","Second": [{"ConfidenceLevel": "Low","Count": 3}]},{"Id": "","Second":[{"ConfidenceLevel": "High","Count": 2}]}]})
];
let result = T
   // 正则方法可行但不允许使用
   // | where tostring(ColumnName) matches regex '"ConfidenceLevel":"High","Count":[^0]'
   | where isnotnull(toscalar(
      print s = tostring(ColumnName) // 此处报错
      | project obj0 = parse_json(s)
      | mv-expand obj1 = obj0.First
      | mv-expand obj2 = obj1.Second
      | where obj2.ConfidenceLevel == "High" and obj2.Count > 0)
      )
        ;
result

正确实现方案 #

使用mv-apply直接在原表列上展开嵌套数组，结合any()聚合函数判断当前行是否存在符合条件的项，无需嵌套子查询。mv-apply会为每行的数组元素生成临时行，any()则会聚合这些临时行的判断结果，保留原行数据。

完整代码示例：

let T = datatable(ColumnName:dynamic)
[
   dynamic({"OtherField": "Unknown","First": [{"Id": "","Second": [{"ConfidenceLevel": "Low","Count": 3}]},{"Id": "","Second":[{"ConfidenceLevel": "High","Count": 0}]}]}),
   dynamic({"OtherField": "Unknown","First": [{"Id": "","Second": [{"ConfidenceLevel": "Low","Count": 3}]},{"Id": "","Second":[{"ConfidenceLevel": "High","Count": 2}]}]})
];
T
| mv-apply firstItem = ColumnName.First on (
    mv-apply secondItem = firstItem.Second on (
        where secondItem.ConfidenceLevel == "High" and secondItem.Count > 0
        | summarize hasMatch = any(true)
    )
)
| where hasMatch == true
| project ColumnName

结果说明 #

运行上述代码后，会选中第二行数据：

ColumnName
{"OtherField":"Unknown","First":[{"Id":"","Second":[{"ConfidenceLevel":"Low","Count":3}]},{"Id":"","Second":[{"ConfidenceLevel":"High","Count":2}]}]}

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