如何将列名传入Kusto的toscalar函数？

问题分析 #

1. toscalar报错原因：toscalar内的子查询是独立执行的，不具备外部表行的上下文，因此无法直接引用外部的ColumnName列，导致列不存在的报错。
2. 正则替代需求：需要通过Kusto原生的JSON嵌套处理语法，筛选出嵌套结构中ConfidenceLevel为"High"且Count>0的行。

解决方案 #

推荐两种合法实现方式，均无需使用正则：

方式一：使用exists子查询（简洁写法）

利用exists检查当前行的嵌套数组中是否存在符合条件的元素：

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
| where exists (
    ColumnName.First
    | mv-expand Second = First.Second
    | where Second.ConfidenceLevel == "High" and Second.Count > 0
)

方式二：使用mv-apply逐行展开嵌套数组

通过mv-apply逐层展开嵌套数组，筛选后保留原行数据：

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 First = ColumnName.First on (
    mv-apply Second = First.Second on (
        where Second.ConfidenceLevel == "High" and Second.Count > 0
    )
)
| where isnotnull(Second)
| project ColumnName

结果验证 #

两种方式均会返回预期的第二行数据：

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