CODEWITHSUNDEEP
azure-data-explorerkqlkusto-explorer
Razi
Razi

Reputation: 1265

How to read JSON field in Kusto query when fields are dynamic

I am working with the JSON data (below) resulting from following query.

SignInLogs
| project AddtionalDetails

Results

[{"value":"test.com","key":"TenantId"},{"value":"PC100921","key":"PolicyId"},{"value":"f4525425-60ff-42a7-acf4-f88c4266431f","key":"ApplicationId"},{"value":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36","key":"Client"},{"value":"SMS","key":"VerificationMethod"},{"value":"+1232123211","key":"PhoneNumber"},{"value":"e000::5890, 128.1.1.1","key":"ClientIpAddress"},{"value":"https://test.com","key":"DomainName"}]

I would like to access access a particular filed, e.g. PolicyId, using query SignInLogs | Policy=extractjson("$.[1].value", tostring(AdditionalDetails)) | project Policy . However, since ordering of fields and their presence is not guaranteed so cant always using [1] as an index.

Is there better way to access JSON fields where ordering and availability is not promised? like in other languages you can check empty reference and access by key name.

Upvotes: 1

Views: 2904

Answers (1)

Avnera
Avnera

Reputation: 7608

Something like this?

let T =datatable(AdditionalDetails:dynamic )[dynamic([{"value":"test.com","key":"TenantId"},{"value":"PC100921","key":"PolicyId"},{"value":"f4525425-60ff-42a7-acf4-f88c4266431f","key":"ApplicationId"},{"value":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36","key":"Client"},{"value":"SMS","key":"VerificationMethod"},{"value":"+1232123211","key":"PhoneNumber"},{"value":"e000::5890, 128.1.1.1","key":"ClientIpAddress"},{"value":"https://test.com","key":"DomainName"}])];
T
| mv-apply AdditionalDetails on ( 
    extend IP = iif(AdditionalDetails.key=="ClientIpAddress", tostring(AdditionalDetails.value), ""), 
           PolicyId = iif(AdditionalDetails.key=="PolicyId", tostring(AdditionalDetails.value), "")
   | where isnotempty(IP) or isnotempty( PolicyId) 
   | summarize take_any(IP), take_any(PolicyId)
)
IP PolicyId
e000::5890, 128.1.1.1 PC100921

Upvotes: 4

Related Questions