How can I reflect hierarchy in output of Azure Resource Graph kusto query?

Question

I am trying to create an Azure Monitor workbook that contains a dropdown parameter that allows you to select a subscription. I know of the existance of the "subscription picker" parameter type but I want to include the names of the management group the subscription belongs to.

So, I have this:

resourcecontainers
| where type == "microsoft.resources/subscriptions"
| extend mgs = properties.managementGroupAncestorsChain
| project name, mgs

which leds to this output:

name	mgs
Sub1	[{"displayName": "Test and Prod Development Group","name": "testprod" }, {"displayName": "Root Group","name": "b266655a-542b-4ab8-8d8f-6ce387638820"}]
Sub2	[{"displayName":"Sandbox","name":"sandbox"},{"displayName":"Root Group","name":"266655a-542b-4ab8-8d8f-6ce387638820"}]

Now, since I cannot build a tree like parameter I want to reflect the hierarchy like this:

name	parent
Sub1	Root Group -> Test and Prod Development Group
Sub2	Root Group -> Sandbox

I've tried to apply the mv-apply operator but after numerous attempts full of parser errors I found out that that operator is not supported for Azure Resource Graph queries.

What are my options, which operator could help me?

Answer 1

you can use mv-expand to achieve it. I understand that you want the subname as column1 and managementgroupancestorchain display values as another colum2. if yes you can use the below query

resourcecontainers 
| where type == "microsoft.resources/subscriptions" 
| extend mgs = properties.managementGroupAncestorsChain 
| mv-expand mgs 
| project name, mgs.displayName,mgs 
| summarize  mgs_dis= make_list(mgs_displayName) by name