Azure LogAnalytics Parse JSON Array

Question

I am ingesting some custom logs to Azure LogAnalytics. One of the columns contains nested json objects. I would like to return each nested object to a separate column value.

Was trying the mvexpand statement but have not had any luck.

customLog_CL
| extend test = parsejson(target_s)
| mvexpand test

The column data looks like below.

[ { "id": "00phb49dl40lBsasC0h7", "type": "PolicyEntity", "alternateId": "unknown", "displayName": "Default Policy", "detailEntry": "@{policyType=hello}" }, { "id": "0pri9mxp9vSc4lpiU0h7", "type": "PolicyRule", "alternateId": "00phb49dl40lBsasC0h7", "displayName": "All Users Login", "detailEntry": null } ]

score 3 · Answer 1 · answered Jul 02 '19 at 07:12

I'm in the exact same situation, so hopefully we can share the knowledge. I ended up doing something like this, if it's the correct way of doing it, or I have any bugs, I honestly can't tell you right now (still doing my data validation, so I'll update later on), but this should at least get you started.

customLog_CL
| mvexpand parsejson(target_s)
| extend Id=target_s["id"]
| extend type=target_s["type"]
| extend OtherId=target_s["alternateId"]
| project Id, type, OtherId

Sharoon Srivastava · Answer 2 · 2019-07-10T22:24:39.813

This should work:

datatable(d:dynamic)  
[  
    dynamic(  
        [  
            { "id": "00phb49dl40lBsasC0h7", "type": "PolicyEntity", "alternateId": "unknown", "displayName": "Default Policy", "detailEntry": "@{policyType=hello}" },   
            { "id": "0pri9mxp9vSc4lpiU0h7", "type": "PolicyRule", "alternateId": "00phb49dl40lBsasC0h7", "displayName": "All Users Login", "detailEntry": "" }  
        ]  
    )  
]  
| mv-expand(d)  
| project key = tostring(d['id']), value = d
| extend p = pack(key, value)
| summarize bag = make_bag(p)
| evaluate bag_unpack(bag)

Output

score 0 · Answer 3 · answered Jun 26 '19 at 16:38

0

Please check if this fits your requirement.

    let hosts_object = parsejson('{"hosts": [ { "id": "00phb49dl40lBsasC0h7", "type": "PolicyEntity", "alternateId": "unknown", "displayName": "Default Policy", "detailEntry": "@{policyType=hello}" }, { "id": "0pri9mxp9vSc4lpiU0h7", "type": "PolicyRule", "alternateId": "00phb49dl40lBsasC0h7", "displayName": "All Users Login", "detailEntry": null } ]}');
    print hosts_object 
    | extend json1 = hosts_object.hosts[0] , json2 = hosts_object.hosts[1]

Output for this should be as below

Additional Documentation Reference

Hope this helps.

answered Jun 26 '19 at 16:38

bharathn-msft

877
5
10

That works, but the results can contain 1 or more objects. So there could be hosts_object.hosts[3] ... to hosts_object.hosts[x] – Sean Jun 26 '19 at 17:14
Maybe the better question here is how to extract each properties in a json string that has multiple nested json objects. Where the nested members could be an infinite number, – Sean Jun 26 '19 at 20:26
Sorry for the delay sean, thanks for providing additional information. Let me further check on this and share if I have any findings. Thanks – bharathn-msft Jul 10 '19 at 14:57

Azure LogAnalytics Parse JSON Array

3 Answers3