List comprehension equivalent in Kusto

Question

I recently started to use the Azure Resource Graph Explorer to obtain resource information. KQL is a new thing I'm figuring out along the way, and one of the problems I need help with is a means to manipulate an array of dictionaries into just an array of string values. As an example:

Consider the following data

{
  "customerId": "201",
  "orders": [
    {
      "dept": "/packaging/fruits"
    },
    {
      "dept": "/packaging/vegetables"
    }
  ]
}

With the following query:

Customers
| where customerId == 201
| project customerId, orders

The result would be as follows:

My question is, how can I modify the query to produce the following result:

Tried to go through the KQL documentation, but can't seem to find the correct method to achieve the above. Any help would be much appreciated!

score 1 · Answer 1 · answered Oct 04 '21 at 16:16

in Kusto, you could use mv-apply:

datatable(customerId:int, orders:dynamic)
[
  201, dynamic([
    {
      "dept": "/packaging/fruits"
    },
    {
      "dept": "/packaging/vegetables"
    }
  ]),
  201, 
  dynamic([
    {
      "dept": "/packaging2/fruits2"
    },
    {
      "dept": "/packaging2/vegetables2"
    }
  ])
]
| where customerId == 201
| mv-apply orders on (
    summarize orders = make_list(orders.dept)
)

customerId	orders
201	[ "/packaging/fruits", "/packaging/vegetables" ]
201	[ "/packaging2/fruits2", "/packaging2/vegetables2" ]

In ARG, mv-apply isn't supported, so you can use mv-expand:

datatable(customerId:int, orders:dynamic)
[
  201, dynamic([
    {
      "dept": "/packaging/fruits"
    },
    {
      "dept": "/packaging/vegetables"
    }
  ]),
  201, 
  dynamic([
    {
      "dept": "/packaging2/fruits2"
    },
    {
      "dept": "/packaging2/vegetables2"
    }
  ])
]
| where customerId == 201
| extend rn = rand()
| mv-expand orders
| summarize orders = make_list(orders.dept) by rn, customerId
| project-away rn

customerId	orders
201	[ "/packaging/fruits", "/packaging/vegetables" ]
201	[ "/packaging2/fruits2", "/packaging2/vegetables2" ]

List comprehension equivalent in Kusto

1 Answers1