How to get data from json column

%3CLINGO-SUB%20id%3D%22lingo-sub-673494%22%20slang%3D%22en-US%22%3EHow%20to%20get%20data%20from%20json%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-673494%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20example%20i%20exec%20bellow%20query%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22MyVMLog%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20ContentData%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20the%20result%20in%20each%20cell%20is%20json%20data%20like%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22%5B%7B%3CBR%20%2F%3E%22time%22%3A%20%222010-07-30T04%3A22%3A25.8051701Z%22%2C%3CBR%20%2F%3E%22category%22%3A%20%22MyEventLogsTable%22%2C%3CBR%20%2F%3E%22level%22%3A%20%22Informational%22%3CBR%20%2F%3E%7D%5D%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20how%20can%20i%20get%20only%20'time'%20field%20for%20each%20cell%20after%20query%20so%20my%20result%20will%20be%20like%3C%2FP%3E%3CP%3ETime%3C%2FP%3E%3CP%3E2010-07-30T04%3A22%3A25.8051701Z%3C%2FP%3E%3CP%3E2012-09-30T04%3A22%3A25.8051701Z%3C%2FP%3E%3CP%3E....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-673494%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-677978%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20data%20from%20json%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-677978%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355690%22%20target%3D%22_blank%22%3E%40hoangn5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESomething%20like%20this%20should%20work%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA0XKMQ6CQBAF0J6EO4xUmhhvsAWhtaCwN5v1BwZxZzM7KBgPbzQa6vfq56Sog%252FGdbSmLFz16KKjxhk50IeeoamXksFSrtioJaoxMG1d9AbMhXqiDnWYjR8lrxnnIEmm79t2nJpUBwX73wLmRWxrZx4CmR7ju%252F6LIMmnAUYI3llgWb%252FaQSvCuAAAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EGo%20to%20Log%20Analytics%20and%20Run%20Query%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20uses%20parse_json%2C%20in%20your%20case%20to%20read%20ContentData%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EAzureActivity%0A%7C%20where%20Category%20%3D%3D%20%22Policy%22%0A%7C%20where%20Properties%20!%3D%22%22%0A%7C%20extend%20getTxt%20%3D%20parse_json%20(Properties)%0A%7C%20project%20getTxt.isComplianceCheck%2C%20getTxt.resourceLocation%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-679890%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20data%20from%20json%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-679890%22%20slang%3D%22en-US%22%3E%3CP%3Ethank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3Ein%20my%20case%20i%20want%20to%20take%20value%20from%20'time'%20attribute%20in%20json%20as%20you%20can%20see%20in%20my%20example.%3CBR%20%2F%3Ebut%20when%20i%20try%20using%26nbsp%3Bparse_json%20path%20as%20'.time'%2C%20the%20'time'%20word%20will%20turn%20blue%20in%20my%20query%20as%20it%20is%20a%20keyword%20and%20when%20i%20exec%20i%20will%20got%20error.%3CBR%20%2F%3EDo%20you%20know%20how%20to%20solve%20it%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-680034%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20get%20data%20from%20json%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-680034%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F355690%22%20target%3D%22_blank%22%3E%40hoangn5%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20might%20need%20to%20put%20quotes%20around%20the%20keyword.%26nbsp%3B%20See%20amended%20example%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EAzureActivity%0A%7C%20where%20Category%20%3D%3D%20%22Policy%22%0A%7C%20where%20Properties%20!%3D%22%22%0A%7C%20extend%20getTxt%20%3D%20parse_json%20(Properties)%0A%7C%20project%20getTxt.isComplianceCheck%2C%20getTxt.%5B'resourceLocation'%5D%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EAzureActivity%0A%7C%20where%20Category%20%3D%3D%20%22Policy%22%0A%7C%20where%20Properties%20!%3D%22%22%0A%7C%20extend%20getTxt%20%3D%20parse_json%20(Properties)%0A%7C%20project%20getTxt.isComplianceCheck%2C%20tostring(getTxt.resourceLocation)%3C%2FPRE%3E%0A%3CP%3EHowever%20%3CSTRONG%3Etime%3C%2FSTRONG%3E%20is%20a%20reserved%20keyword%2C%20so%20I%20suspect%20the%20above%20might%20not%20work.%26nbsp%3B%20Is%20the%20original%20data%20source%20changeable%2C%20%22Time%22%20with%20an%20upper%20case%20%22T%22%20would%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@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
Highlighted

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?

Highlighted

@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?