azure - Split KQL array into multiple columns

Question

I'm trying to build a dashboard in Azure Sentinel's workbook. One of the columns is a JSON Array of varying length. I'd like to split that array so that each element in the array becomes its own column, but I can't figure out a good way to do that. Basically I want this

-----------------------------------
Company | products
-----------------------------------
Apple     | [iMac, iPhone, iPad]
Microsoft | [xBox, xBox 360]
Google    | [Chromebook]
--------------------------------------

To become

-----------------------------------
Company | product_1 | product_2 | product_3
-----------------------------------
Apple | iMac        |iPhone     |iPad
Microsoft | xBox    |xBox 360   |
Google | Chromebook |           |
--------------------------------------

question from:https://stackoverflow.com/questions/65911686/split-kql-array-into-multiple-columns

Answer 1

not highly-efficient, but here's an option:

datatable(company:string, products:dynamic)
[
    "Apple",     dynamic(['iMac', 'iPhone', 'iPad']),
    "Microsoft", dynamic(['xBox', 'xBox 360']),
    "Google",    dynamic(['Chromebook']),
]
| mv-apply with_itemindex=i products on (
    extend p = pack(strcat("product_", i+1), products)
    | summarize b = make_bag(p)
)
| evaluate bag_unpack(b)
| project-reorder company, product* asc

-->

| company   | product_1  | product_2 | product_3 |
|-----------|------------|-----------|-----------|
| Apple     | iMac       | iPhone    | iPad      |
| Microsoft | xBox       | xBox 360  |           |
| Google    | Chromebook |           |           |