英文:
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 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论