KQL: How to create new fields of keys and values from a field with JSON object?

Question

I have a field in Azure monitor log which is in JSON format, and here is an example:

{
  "industry": ["Health"],
  "autoAbstractRegions": ["United States"],["Canada"] 
}

Can someone teach me how to create two new fields of keys and values from this? I also need to use mv-expand to expands the original rows into separate rows for each value.

I managed to create a new field of keys by using:

| extend keys = bag_keys(Filters)

| mv-expand keys 

However, I have no clue how create the field of value.

Answer 1

To create key and values field from the JSON field, you can use mv-apply operator. Below is the example to do this.

let MyTable = datatable(MyJson:dynamic)
[
    dynamic({
        "industry": ["Health"],
        "autoAbstractRegions": ["United States", "Canada"]
    })
];
MyTable
| mv-apply MyJson on(
extend key = tostring(bag_keys(MyJson)[0])
 | project key, value = MyJson[key]
 )
 | mv-expand value

In this query, the mv-apply operator is used to apply a subquery to each row of the MyJson column. The subquery uses the extend operator to create a new field of keys, and the project operator to project the keys and values into separate columns. The mv-expand operator is then used to expand the values into separate rows.

Output:

key	value
industry	Health
autoAbstractRegions	United States
autoAbstractRegions	Canada