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

Yoni L. · Accepted Answer · 2021-04-21 18:52:03Z

2

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
)

edited Apr 21, 2021 at 18:52

answered Apr 21, 2021 at 15:40

Yoni L.

26.5k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

John Smith Over a year ago

Hi Yoni, not really sure I understand. What is the (d:dynamic) and the other "dynamic"? Sorry to sound a bit dumb, but I also dont understand what the "d on" is? What does mv-apply mean?

John Smith Over a year ago

I tried this, but it doesnt work... InformationProtectionLogs_CL | mv-apply DiscoveredInformationTypes_s on (where DiscoveredInformationTypes_s.Confidence > 80)

Yoni L. Over a year ago

if DiscoveredInformationTypes_s is a string and not a dynamic column, you'll need to first make it dynamic (e.g. by invoking parse_json() on it)

John Smith Over a year ago

Hi, sorry, but its not that its a string, I dont understand why you have used "d" .. what is d? Ive looked at the syntax on that MS page and I dont understand the terms. Why is there not a simple way just to query the value of one of these list pairs? :( This seems far more complex than needed. Dont get it.

Yoni L. Over a year ago

d is just a name of a dynamic-typed column in the example (i've now renamed it to match your original column name - DiscoveredInformationTypes_s is, if that helps). i'm using an inline datatable for the sake of providing you with a reproducible example/query, as obviously only you have access to your table

|

Collectives™ on Stack Overflow

Kusto Query JSON Array Values

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related