How can I find a specific element in a dynamic array in KQL?

How can I find a specific element in a dynamic array in KQL?

E.g. I have the json array (i.e. a string) [{"key": "foo", "val": "bar"}, {"key": "a", "val": "b"}] in KQL. Now I want to find out the value of the property val for the objects with the "a" key. It can be at any index, not only at the index 1 as it is in the example above.

I found the array_index_of function. But it does not seem to accept any predicates, it accepts only the index and I don't know the index in my case beforehand.

Are there any means to query the JSON array for an item which satisfies some conditions in KQL?

I tried the mv-expand, but it seems to separate the key from the val:

datatable (b: dynamic)
[
    dynamic({"key": "foo", "val": "bar"}), 
    dynamic({"key": "a", "val": "b"})
]
| mv-expand b

In KQL, you can use the mv-expand operator to work with dynamic arrays and then use the mv-apply operator to filter the elements based on a condition. In your case, so to find value of 'var' with the key of 'a' you would want something in the area of:

    datatable jsonString string
[
    '{"key": "foo", "val": "bar"}, {"key": "a", "val": "b"}',
    '{"key": "x", "val": "y"}, {"key": "a", "val": "z"}'
]
| mv-apply parsedJson = parse_json(jsonString) on (
    project key = tostring(parsedJson['key']), val = tostring(parsedJson['val'])
)
| where key == "a"
| project val

mv-apply parsedJson = parse_json(jsonString) on (...) The parse_json function is used to convert the JSON strings in the "jsonString" column into objects. The mv-apply operator is then used to apply this parsing to each row of the datatable, creating new columns "key" and "val" with extracted values.

where key == "a" This filters the rows to keep only those where the "key" is "a"

After executing the query, you'll get a table containing the values for the objects with the "a" key

Edit:
To get the key and val on the same row, you can use the mv-apply operator with the project statement. This will let you apply a projection to each element of the dynamic array and merge the properties key and val into a single row.

    datatable jsonString string
[
    '{"key": "foo", "val": "bar"}, {"key": "a", "val": "b"}',
    '{"key": "x", "val": "y"}, {"key": "a", "val": "z"}'
]
| mv-apply parsedJson = parse_json(jsonString) on (
    project key = tostring(parsedJson['key']), val = tostring(parsedJson['val'])
)
| where key == "a"
| project key, val

Output for me with my example data is:

| key | val |
|-----|-----|
| a   | b   |
| a   | z   |

>Now I want to find out the value of the property val for the objects with the "a" key.

I have reproduced in my environment and below are expected results:

You can just use bag_unpack() and then get the value of a like below:

datatable (b: dynamic)
[
    dynamic({"key": "foo", "val": "bar"}), 
    dynamic({"key": "a", "val": "b"})
]
|evaluate bag_unpack(b)
| where key contains "a"

Output:

Fiddle.

>Do you happen to know, why in my example above does it split one dynamic into two pieces? E.g. the key foo and the val bar end up in different rows.
>
mv-exapnd divides the dynamic array based on commas "," and new line so it divides like that and this is an expected behavior.