azure - 如何使运算符对列名称不区分大小写？

Question

我正在尝试project列，但列标题没有一致的大小写。如何将它们合并到一列中？我尝试使用 tolower在几个地方，

(示例)

tolower(parse_json(tostring(parse_json(Properties).requestbody))).Properties.PrincipalType

但是收到类似的错误

Path expression Properties_PrincipalType source must be scalar of type 'dynamic'. Received a source of type string instead .

另外，我不确定这样的线是投影列的最佳方式吗？ parse_json(tostring(parse_json(Properties).requestbody)).Properties.PrincipalType

示例

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"} )
]
| project OperationName, 
    PrincipalType1 = parse_json(tostring(parse_json(Properties).requestbody)).Properties.PrincipalType, 
    PrincipalType2 = parse_json(tostring(parse_json(Properties).requestbody)).properties.principalType, 
    PrincipalId1 = parse_json(tostring(parse_json(Properties).requestbody)).Properties.PrincipalId, 
    PrincipalId2 = parse_json(tostring(parse_json(Properties).requestbody)).properties.principalId

结果

<表类=“s-表”><标题>操作名称主体类型1主体类型2PrincipalId1PrincipalId2 <正文>创建角色分配组b38e5be0-fff5-4de3-8a89-069ab41495d1创建角色分配组8fb360df-a2ce-4cab-810f-e68a95876032

期望的结果

<表类=“s-表”><标题>操作名称主体类型PrincipalId <正文>创建角色分配组b38e5be0-fff5-4de3-8a89-069ab41495d1创建角色分配组8fb360df-a2ce-4cab-810f-e68a95876032

Answer 1

如果只有 2 个事先已知的大小写选项(例如小写 p 和大写 P ) - 您可以使用 coalesce() function .

例如:

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"})
]
| 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)

<表类=“s-表”><标题>操作名称主体类型PrincipalId <正文>创建角色分配组b38e5be0-fff5-4de3-8a89-069ab41495d1创建角色分配组8fb360df-a2ce-4cab-810f-e68a95876032