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"

Answer 1

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