Question 220383 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – 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

enter image description here

Tags:

2

Answers


  1. 0

    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   |
    Login or Signup to reply.
  2. 0

    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"

    enter image description here

    Output:

    enter image description here

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search