Azure Data Explorer - Reading JSON Data Using Kusto

Introduction

You may have a requirement where you have data stored in a column in JSON format, and the business need is to read that column value. When it comes to JSON, there are a few ways that can help us to read this data and represent it in a meaningful and readable manner.

Let’s consider the below sample data from the table named demoData.

In the above table, the last column named Description is holding the data in JSON format.

Using Dynamic

One way to extract data from the Description column is by using the dynamic literal as shown in the below query.

demoData 
| extend AllProperties = todynamic(Description) 
| project Environment, BugId = AllProperties["Id"], AssignedTo = AllProperties["AssignedTo"]

On execution of the above query, you will notice that all the properties of JSON are extracted in the form of new columns as shown below.

We can further improvise the above query in terms of readability. If the column title and the JSON property have the same name, then the JSON property can be directly accessed using the dot as shown below for AssignedTo,

demoData
| extend AllProperties = todynamic(Description)
| project Environment, BugId = AllProperties["Id"], AssignedTo = AllProperties.AssignedTo

The result of the above query would also be the same as shown above.

Using parse_json

Sometimes, we do have a requirement to extract just one or two properties from the JSON column. In such a scenario, reading the entire JSON value and converting it would be an expensive operation. Here comes the parse_json to rescue us. Below is the sample query to achieve this.

demoData 
| extend AssignedTo = tostring(parse_json(Description)["AssignedTo"])
| project Environment, ItemId, AssignedTo

On execution of the above query, the below result can be achieved.

Hope you enjoyed extracting JSON data.

Happy Kustoing!