如何在Kusto中动态解析带数字键的嵌套JSON对象

需要解析list对象下的嵌套数字键JSON对象（数字键为动态生成，范围1到10），实现扁平化该对象并提取数据，例如获取machineinfo.last.netadapter.list.[0-2].name对应的所有名称。

示例JSON #

"machineinfo": {
    "last": {
        "error": "0",
        "netadapter": {
            "count": "3",
            "list": {
                "0": {
                    "name": "Intel(R) Wi-Fi 6E AX211 160MHz",
                    "macaddress": "11:11:11:11:11:11:test"
                },
                "1": {
                    "name": "Microsoft Wi-Fi Direct Virtual Adapter",
                    "macaddress": "00:00:00:00:00:test"
                },
                "2": {
                    "name": "Bluetooth Device (Personal Area Network)",
                    "macaddress": "22:22:22:22:22:test"
                }
            }
        },
        "bios": {
            "version": "test - 1111",
            "manufacturer": "Test Inc.",
            "sn": "test"
        },
        "motherboard": {
            "manufacturer": "test",
            "sn": "testvsdfa",
            "product": "test123"
        }
    }
}

当前尝试的KQL语句 #

RemoteAccess | take 100  | project MyJSON = todynamic(machineinfo.['last'].['netadapter'].['list']) | project MyJSONFirst= MyJSON["0"] | extend name1= tostring(MyJSONFirst.name)

该语句仅能提取单个数字键（如0）对应的数据，无法批量处理动态生成的所有数字键。

解决方案 #

方法1：自动遍历所有存在的数字键 #

利用bag_keys获取JSON对象的所有键，再通过mv-apply逐个处理：

RemoteAccess
| take 100
| extend netadapter_list = todynamic(machineinfo.last.netadapter.list)
| mv-apply key = bag_keys(netadapter_list) on (
    extend adapter = netadapter_list[key]
    | project name = adapter.name, macaddress = adapter.macaddress, 键值 = key
)

• extend netadapter_list：将目标list对象转换为动态类型
• bag_keys(netadapter_list)：提取list对象的所有键
• mv-apply：遍历每个键，取出对应的适配器对象，提取name、macaddress及原键值

方法2：指定键范围遍历（已知范围1-10） #

如果明确数字键范围是0-10，可通过range生成键列表，再过滤存在的键：

RemoteAccess
| take 100
| extend netadapter_list = todynamic(machineinfo.last.netadapter.list)
| mv-apply 键值 = range(0, 10, 1) on (
    extend key_str = tostring(键值)
    | where bag_has_key(netadapter_list, key_str)
    | extend adapter = netadapter_list[key_str]
    | project name = adapter.name, macaddress = adapter.macaddress, 键值
)

• range(0,10,1)：生成0到10的连续数字作为候选键
• bag_has_key：检查候选键是否存在于list对象中，避免处理不存在的键

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