Kusto查询：如何实现列名不区分大小写并合并同含义列？

问题背景 #

在Kusto查询中使用project操作列时，遇到同含义列标题大小写不统一（如Properties.PrincipalType和properties.principalType），需要合并为单一列。尝试使用tolower函数处理时收到错误：Path expression Properties_PrincipalType source must be scalar of type 'dynamic'. Received a source of type string instead，同时不确定当前重复解析JSON的列投影方式是否最优。

问题原因 #

1. tolower报错原因：tolower会将dynamic类型的JSON对象转换为字符串，后续无法再通过点符号访问dynamic属性，导致类型不匹配错误。
2. 重复解析的性能问题：原查询中多次调用parse_json(tostring(parse_json(Properties).requestbody))重复解析同一JSON内容，会降低查询效率。

解决方案 #

1. 提前解析JSON：通过extend先将requestbody解析为dynamic类型的中间列，避免重复解析。
2. 合并大小写属性：使用coalesce函数，依次尝试访问不同大小写的属性，返回第一个非空值，实现同含义列的合并。

优化后的查询 #

datatable(OperationName:string, Properties:dynamic)
[
    "Create role assignment", dynamic( {"requestbody":"{\"Id\":\"6c646e88-b034-4486-8e77-b62b2aaffd75\",\"Properties\":{\"PrincipalId\":\"b38e5be0-fff5-4de3-8a89-069ab41495d1\",\"PrincipalType\":\"Group\",\"RoleDefinitionId\":\"/providers/Microsoft.Authorization/roleDefinitions/f94d2a34-3e3c-4a04-acab-beaffc4d55ed\",\"Scope\":\"/subscriptions/0a21e950-3eea-45cc-9424-c412d1c89c5f/resourceGroups/rg-01\",\"Condition\":null,\"ConditionVersion\":null}}","eventCategory":"Administrative","entity":"/subscriptions/fd78615b-5c34-4858-a974-c7342e5aa0e5/resourceGroups/rg-01/providers/Microsoft.Authorization/roleAssignments/d7408255-6c7c-4bec-8c4d-bb589d5bb92c","message":"Microsoft.Authorization/roleAssignments/write","hierarchy":"3f8525ff-c00d-459b-8c75-28ecc60c70d4/Subscription01/f848451c-bc59-4fa8-84db-02ab33ff2aa8"} ),
    "Create role assignment", dynamic( {"requestbody":"{\"properties\":{\"roleDefinitionId\":\"/subscriptions/9acda363-2bb6-4482-82be-5d9792bf3927/resourceGroups/rg-02/providers/Microsoft.Authorization/roleDefinitions/b48dfeca-c01b-4b03-8e3e-8a8d21ae027d\",\"principalId\":\"8fb360df-a2ce-4cab-810f-e68a95876032\",\"principalType\":\"Group\"}}","eventCategory":"Administrative","entity":"/subscriptions/afc975b2-97f3-4717-a20e-7d53b60cbd08/resourceGroups/rg-02/providers/Microsoft.Authorization/roleAssignments/ab8bbf8f-2336-483e-b49f-ed6815ca303b","message":"Microsoft.Authorization/roleAssignments/write","hierarchy":"3ba18132-011f-4707-bf07-e5c9d8ad520e/Subscription01/fac4b1ec-2224-45ca-a30d-75e5eea5046c"} )
]
// 提前解析requestbody为dynamic中间列，避免重复解析
| extend RequestBody = parse_json(tostring(Properties.requestbody))
| project 
    OperationName,
    PrincipalType = coalesce(RequestBody.Properties.PrincipalType, RequestBody.properties.principalType),
    PrincipalId = coalesce(RequestBody.Properties.PrincipalId, RequestBody.properties.principalId)

执行结果 #

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