Kusto Query JSON Array Values

Question

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)" } ] 

Answer 1

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
)