如何从Kusto查询语言中的JSON数组中提取特定值?

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

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 []

huangapple
  • 本文由 发表于 2023年6月2日 06:48:20
  • 转载请务必保留本文链接:https://go.coder-hub.com/76386160.html
匿名

发表评论

匿名网友

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

确定