can anyone offer a clue on how to do query values within arrays -- such as below, I want to find all records where
DiscoveredInformationTypes_s Confidence > 80
Can anyone help? How do I query inside this array?
MachineName_s
Version_s
ProcessName_s
ApplicationName_s
Operation_s
ObjectId_s
DiscoveredInformationTypes_s
[ { "Confidence": 55, "Count": 1, "SensitiveType": "3356946c-6bb7-449b-b253-6ffa419c0ce7", "UniqueCount": 1, "SensitiveInformationDetections": null, "Name": "International Classification of Diseases (ICD-10-CM)" } ]
you can use mv-apply: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/mv-applyoperator
for example:
datatable(DiscoveredInformationTypes_s:dynamic)
[
dynamic([ { "Confidence": 55, "Count": 1, "SensitiveType": "3356946c-6bb7-449b-b253-6ffa419c0ce7", "UniqueCount": 1, "SensitiveInformationDetections": null, "Name": "International Classification of Diseases (ICD-10-CM)" } ]),
dynamic([ { "Confidence": 81, "Count": 1, "SensitiveType": "3356946c-6bb7-449b-b253-6ffa419c0ce7", "UniqueCount": 1, "SensitiveInformationDetections": null, "Name": "International Classification of Diseases (ICD-10-CM)" } ])
]
| mv-apply DiscoveredInformationTypes_s on (
where DiscoveredInformationTypes_s.Confidence > 80
)
or, if your column is string-typed and not dynamic-typed, you'll need to invoke parse_json() on it first:
datatable(s:string)
[
'[ { "Confidence": 55, "Count": 1, "SensitiveType": "3356946c-6bb7-449b-b253-6ffa419c0ce7", "UniqueCount": 1, "SensitiveInformationDetections": null, "Name": "International Classification of Diseases (ICD-10-CM)" } ]',
'[ { "Confidence": 81, "Count": 1, "SensitiveType": "3356946c-6bb7-449b-b253-6ffa419c0ce7", "UniqueCount": 1, "SensitiveInformationDetections": null, "Name": "International Classification of Diseases (ICD-10-CM)" } ]'
]
| project DiscoveredInformationTypes_s = parse_json(s)
| mv-apply DiscoveredInformationTypes_s on (
where DiscoveredInformationTypes_s.Confidence > 80
)