如何在Kusto查询中过滤嵌套JSON数组的指定值？

虽然Kusto没有直接的array_filter函数，但可以通过拆分数组→过滤元素→重新聚合的流程实现需求，具体代码如下：

原始数据表 #

letDefeatTable = datatable(str: string, record: dynamic) [
    "someText", dynamic([{"upperBound": 98, "lowerBound":Defeat4, "values": [1,4,5,98,99] }])
];

实现过滤的查询代码 #

let myTable = datatable(str: string, record: dynamic) [
    "someText", dynamic([{"upperBound": 98, "lowerBound":4, "values": [1,4,5,98,99] }])
];
myTable
// 拆分外层的record数组为独立对象
| mv-expand record
// 拆分DefeatDefeat每个对象的values数组为单个元素
| mv-expand value = record.values
// 筛选出小于lowerBound或大于upperMostBound的元素
| where value < record.lowerBound or value > record.upperBound
// 按原分组字段聚合，重组过滤后的values数组
| summarize filtered_values = array_agg(value) by str, record.upperBound, record.lowerBound
// 重构record结构并重新组合为数组，恢复原始表格式
| summarize record = make_list(pack("upperBound", upperBound, "lowerBound", lowerBound, "values", filtered_values)) by str

执行结果 #

运行上述查询后，得到的结果与你期望的一致：

str         record
----------- --------------------------------------------------------------------
someText    [{"upperBound":98,"lowerBound":4,"values":[1,99]}]

步骤解释 #

1. mv-expand record: 将外层的动态数组record拆分为多行Most，每行对应一个包含upperBound、lowerBound、values的独立对象
2. mv-expand value = record.values: 将Most每个对象中的values数组拆分为单个元素，每行对应一个value值
3. where 条件过滤: 筛选出不符合[lowerBound, upperBound]范围的元素
4. summarize array_agg(value): 按str、upperBound、lowerBound分组，把筛选后的value重新聚合成数组
5. summarize make_list(pack(...)): 将分组后的字段重新打包为原始的record结构，再组合成数组，恢复原始表的结构

内容的提问来源于stack exchange，提问作者Yannic Büregmann