如何使操作符对列名不区分大小写?

huangapple go评论59阅读模式
英文:

How do I make operator not case-sensitive on column names?

问题

我尝试“project”一个列,但列标题的大小写不一致。如何将它们合并成一个单独的列?我尝试在多个地方使用 tolower,但遇到类似这样的错误:

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

结果

OperationName PrincipalType1 PrincipalType2 PrincipalId1 PrincipalId2
Create role assignment Group b38e5be0-fff5-4de3-8a89-069ab41495d1
Create role assignment Group 8fb360df-a2ce-4cab-810f-e68a95876032

期望的结果

OperationName PrincipalType PrincipalId
Create role assignment Group b38e5be0-fff5-4de3-8a89-069ab41495d1
Create role assignment Group 8fb360df-a2ce-4cab-810f-e68a95876032
英文:

I'm trying to project a column, but the column headings don't have a consistent casing. How can I merge these into a single column? I tried using tolower in several places,

(example)

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

But receiving an error like

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

Also, I'm not sure that a line like this is the most optimal way to project the column?
parse_json(tostring(parse_json(Properties).requestbody)).Properties.PrincipalType

Sample

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

Result

OperationName PrincipalType1 PrincipalType2 PrincipalId1 PrincipalId2
Create role assignment Group b38e5be0-fff5-4de3-8a89-069ab41495d1
Create role assignment Group 8fb360df-a2ce-4cab-810f-e68a95876032

Desired result

OperationName PrincipalType PrincipalId
Create role assignment Group b38e5be0-fff5-4de3-8a89-069ab41495d1
Create role assignment Group 8fb360df-a2ce-4cab-810f-e68a95876032

答案1

得分: 1

如果已经提前知道只有2种大小写选项(例如小写 p 和大写 P),您可以使用 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"})
]
| 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)
OperationName PrincipalType PrincipalId
Create role assignment Group b38e5be0-fff5-4de3-8a89-069ab41495d1
Create role assignment Group 8fb360df-a2ce-4cab-810f-e68a95876032
英文:

if there are only 2 casing options that are known in advance (e.g. lowercase p and uppercase P) - you can use the coalesce() function.

for example:

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)
OperationName PrincipalType PrincipalId
Create role assignment Group b38e5be0-fff5-4de3-8a89-069ab41495d1
Create role assignment Group 8fb360df-a2ce-4cab-810f-e68a95876032

huangapple
  • 本文由 发表于 2023年3月4日 00:04:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/75629367.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定