Azure Knowledge Explorer – Studying JSON Knowledge Utilizing Kusto



You’ll have a requirement the place you’ve gotten information saved in a column in JSON format, and the enterprise want is to learn that column worth. On the subject of JSON, there are just a few methods that may assist us to learn this information and signify it in a significant and readable method.


Let’s contemplate the beneath pattern information from the desk named demoData:



Within the above desk, the final column named Description is holding the info in JSON format.


Utilizing Dynamic


One solution to extract information from the Description column is by utilizing the dynamic literal as proven within the beneath question:

  1. demoData 
  2. | lengthen AllProperties = todynamic(Description)  
  3. |¬†mission¬†Setting,¬†BugId¬†=¬†AllProperties[“Id”],¬†AssignedTo¬†=¬†AllProperties[“AssignedTo”]¬†¬†

On execution of the above question, you’ll discover that each one the properties of JSON are extracted within the type of new columns as proven beneath,


Azure Data Explorer - Reading JSON Data Using Kusto


We are able to additional improvise the above question by way of readability. If the column title and the JSON property are having the identical title, then the JSON property might be straight accessed utilizing dot as proven beneath for AssignedTo,

  1. demoData  
  2. | lengthen AllProperties = todynamic(Description)  
  3. |¬†mission¬†Setting,¬†BugId¬†=¬†AllProperties[“Id”],¬†AssignedTo¬†=¬†AllProperties.AssignedTo¬†¬†

The results of the above question would even be the identical as proven above.


Utilizing parse_json


Typically, we do have a requirement to extract only one or two properties from the JSON column. In such a state of affairs, studying all the JSON worth and changing it could be an costly operation. Right here comes the parse_json to rescue us. Under is the pattern question to realize this:

  1. demoData   
  2. |¬†lengthen¬†AssignedTo¬†=¬†tostring(parse_json(Description)[“AssignedTo”])¬†¬†
  3. | mission Setting, ItemId, AssignedTo  

On execution of the above question, the beneath outcome might be achieved:


Azure Data Explorer - Reading JSON Data Using Kusto


Hope you loved extracting JSON information.


Blissful Kustoing!

Show More

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button