How to get data from json column

New Contributor

For example i exec bellow query:

 

"MyVMLog

| project ContentData"

 

And the result in each cell is json data like 

 

"[{
"time": "2010-07-30T04:22:25.8051701Z",
"category": "MyEventLogsTable",
"level": "Informational"
}]"

 

So how can i get only 'time' field for each cell after query so my result will be like

Time

2010-07-30T04:22:25.8051701Z

2012-09-30T04:22:25.8051701Z

....

3 Replies

@hoangn5 

 

Something like this should work: Go to Log Analytics and Run Query

 

It uses parse_json, in your case to read ContentData 

AzureActivity
| where Category == "Policy"
| where Properties !=""
| extend getTxt = parse_json (Properties)
| project getTxt.isComplianceCheck, getTxt.resourceLocation

thank you @Clive Watson ,

in my case i want to take value from 'time' attribute in json as you can see in my example.
but when i try using parse_json path as '.time', the 'time' word will turn blue in my query as it is a keyword and when i exec i will got error.
Do you know how to solve it?

@hoangn5 

 

You might need to put quotes around the keyword.  See amended example

 

AzureActivity
| where Category == "Policy"
| where Properties !=""
| extend getTxt = parse_json (Properties)
| project getTxt.isComplianceCheck, getTxt.['resourceLocation']

or

 

AzureActivity
| where Category == "Policy"
| where Properties !=""
| extend getTxt = parse_json (Properties)
| project getTxt.isComplianceCheck, tostring(getTxt.resourceLocation)

However time is a reserved keyword, so I suspect the above might not work.  Is the original data source changeable, "Time" with an upper case "T" would work?