Forum Discussion

hoangn5's avatar
hoangn5
Copper Contributor
Jun 06, 2019

How to get data from json column

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

....

  • 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
    • hoangn5's avatar
      hoangn5
      Copper Contributor

      thank you CliveWatson ,

      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?

      • CliveWatson's avatar
        CliveWatson
        Icon for Microsoft rankMicrosoft

        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?

Resources