Azure Data Explorer - Reading JSON Data Using Kusto

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

Let’s consider below sample data:

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

Using Dynamic

One way to extract data of description column is by using the dynamic literal as shown in below query:

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

On execution of 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 are having the same name, then JSON property can be directly accessed using dot as shown below for AssignedTo:

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

The result of 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 JSON column. In such scenario, reading 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:

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

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

Hope you enjoyed extracting JSON data.

Happy kustoing!