英文:
How can I extract specific values from a JSON array in Kusto Query Language?
问题
| mv-expand DProducts
| project OrgId, OrgName, DProducts_name = tostring(parse_json(DProducts).Name)
英文:
Kusto Query to parse JSON array and gather all values of a given property
What is the best way to query a specific key values in an JSON array. Here is a sample input of two rows, where the third column 'DProducts' is dynamic column
OrgId, OrgName, DProducts (dynamic)
581, ABC, [{"Id":"123","Name":"abc","PlanId":"af3"},{"Id":"234","Name":"bcd","PlanId":"aee3"}]
582, ABCD, [{"Id":"12345","Name":"abcfg","PlanId":"afg3"},{"Id":"234","Name":"bcd","PlanId":"aee3"}]
583, CDEF, []
Looking to generate an output in the below format.
OrgId, OrgName, DProducts_name
581, ABC, ["abc" , "bcd"]
582, ABCD, ["abcfg" , "bcd"]
583, CDEF, []
Tried
| mv-expand DProducts
| project OrgId, OrgName,DProducts["Name"]
This returns new row for each element name from the json
i.e
OrgId, OrgName, DProducts_name
581, ABC, "abc"
581, ABC, "bcd"
582, ABCD, "abcfg"
582, ABCD, "bcd"
答案1
得分: 1
你可以使用mv-apply
运算符和make_list()
聚合函数的组合。
例如:
datatable(OrgId: int, OrgName: string, DProducts: dynamic)
[
581, 'ABC', dynamic([{"Id": "123", "Name": "abc", "PlanId": "af3"}, {"Id": "234", "Name": "bcd", "PlanId": "aee3"}]),
582, 'ABCD', dynamic([{"Id": "12345", "Name": "abcfg", "PlanId": "afg3"}, {"Id": "234", "Name": "bcd", "PlanId": "aee3"}]),
583, 'CDEF', dynamic([]),
]
| mv-apply DProducts on (
project name = DProducts.Name
| summarize DProducts_name = make_list(name)
)
OrgId | OrgName | DProducts_name |
---|---|---|
581 | ABC | ["abc", "bcd"] |
582 | ABCD | ["abcfg", "bcd"] |
583 | CDEF | [] |
英文:
you could use a combination of the mv-apply
operator and the make_list()
aggregation function.
for example:
datatable(OrgId: int, OrgName: string, DProducts: dynamic)
[
581, 'ABC', dynamic([{"Id": "123", "Name": "abc", "PlanId": "af3"}, {"Id": "234", "Name": "bcd", "PlanId": "aee3"}]),
582, 'ABCD', dynamic([{"Id": "12345", "Name": "abcfg", "PlanId": "afg3"}, {"Id": "234", "Name": "bcd", "PlanId": "aee3"}]),
583, 'CDEF', dynamic([]),
]
| mv-apply DProducts on (
project name = DProducts.Name
| summarize DProducts_name = make_list(name)
)
OrgId | OrgName | DProducts_name |
---|---|---|
581 | ABC | [<br> "abc",<br> "bcd"<br>] |
582 | ABCD | [<br> "abcfg",<br> "bcd"<br>] |
583 | CDEF | [] |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论