KQL - Extracting nested JSON array that's missing keys to columns

Question

I've got some logs from applications with the following format:

LogEntry:{"category1/sub_category": [{"item_description": "item1", "item_price": 1.2, "sku": 12}], "category2/sub_category": [{"item_description": "item2", "item_price": 5.0, "sku": 123},{"item_description": "item3", "item_price": 5.0, "sku": 1234}]}

It looks like a json array but it's missing a key for the category/sub_category values cos I want to convert it into a table structure like this:

Category	item_description	item_price	sku
category1/sub_category	item1	1.2	12
category2/sub_category	item2	5.0	123
category2/sub_category	item3	5.0	1234

I've tried parse_json() then mv-expand but the issue is Category/sub_category would become column names not values..

Any help would be greatly appreciated! Cheers

Answer 1

Try below code to convert json into Tabular format:

let result = datatable ( newRes : dynamic )
[
    dynamic({
        "category1/sub_category": [
            {
                "item_description": "item1", 
                "item_price": 1.2, 
                "sku": 12
            }
        ], 
          "category2/sub_category": [
            {
              "item_description": "item2", 
              "item_price": 5.0, 
              "sku": 123
            },
            {
              "item_description": "item3", 
              "item_price": 5.0, 
              "sku": 1234
            }
        ]
    })
];
result
| mv-apply newRes on (extend category = tostring(bag_keys(newRes)[0])
|project category, values = newRes[category])
| mv-expand values
| extend item_description = values.item_description
| extend item_price = values.item_price
| extend sku = values.sku
| project-away values

use mv-apply, as it applies a subquery to each record and returns the union of the results of all subqueries. By using bag_keys, it establishes the root keys in a dynamic property bag object and mv-expand is used to expand multi-value dynamic arrays into multiple records, as shown in the below output:

Category                 | item_description | item_price | sku
-------------------------|------------------|------------|-----
category1/sub_category   | item1            | 1.2        | 12
category2/sub_category   | item2            | 5.0        | 123
category2/sub_category   | item3            | 5.0        | 1234