How can I extract specific values from a JSON array in Kusto Query Language?

Question

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"

Yoni L. · Accepted Answer · 2023-06-01 23:10:23Z

2

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	[ "abc", "bcd" ]
582	ABCD	[ "abcfg", "bcd" ]
583	CDEF	[]

answered Jun 1, 2023 at 23:10

Yoni L.

26.5k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sambe Over a year ago

Thank you Yoni L. Your suggestion worked !!. Appreciate your time and efforts

Collectives™ on Stack Overflow

How can I extract specific values from a JSON array in Kusto Query Language?

Kusto Query to parse JSON array and gather all values of a given property

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Kusto Query to parse JSON array and gather all values of a given property

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related