Kusto KQL: how to check if JSON array in dataset contains element of another array?

user_304870 0
Viewed 697
  • The dataset (table) I'm querying has a column containing a JSON string array.
  • I have a fixed list of verbs which I need to check against each entry in the table and find those, where at least one of the items in the JSON list starts with one of the verbs from the fixed list.
// Verbs to look for (actual list is longer).
let verbs = datatable (verb : string) [
"discover",
"gain"
];

// Data. Second column is a JSON string.
let data = datatable(id : int, json: string) [
1, "[\"Discover me\", \"some text\"]",
2, "[\"All good\", \"no invalid verbs\"]",
3, "[\"first element fine\", \"gain power isn't ok\"]",
];

// Query: I need to know if at least one of the items in the "json" column starts
// with one of the verbs of the "verbs" list.
data
| extend  parsedJson = parse_json(json)
| extend OneOrMoreListItemsHaveVerb = false
| project id, OneOrMoreListItemsHaveVerb

I tried to use mv_apply() but failed because I'm dealing with two lists/arrays compared against each other, not one array and one item.

For the example data above, I expect items with IDs 1 and 3 to be returned. The first element of item 1 has "discover" and the 2nd element of item 3 starts with "gain".

kql azure-data-explorer
1 Answers

you could create an array from your input table (e.g. using summarize make_set()), then loop over it using mv-apply foreach of the inputs.

for example:

let verbs = datatable (verb: string) [
    "discover", "gain"
]
;
let verbs_list = toscalar(verbs | summarize make_set(verb))
;
let data = datatable(id: int, json: string) [
    1, "[\"Discover me\", \"some text\"]",
    2, "[\"All good\", \"no invalid verbs\"]",
    3, "[\"first element fine\", \"gain power isn't ok\"]",
]
;
data
| mv-apply verb = verbs_list on (
    mv-apply input = parse_json(json) on (
        where input startswith verb
    )
)
| project ['id'], json
id json
1 ["Discover me", "some text"]
3 ["first element fine", "gain power isn't ok"]

alternatively, you can implement similar logic using the partition operator:

let verbs = datatable (verb: string) [
    "discover", "gain"
]
;
let data = datatable(id: int, json: string) [
    1, "[\"Discover me\", \"some text\"]",
    2, "[\"All good\", \"no invalid verbs\"]",
    3, "[\"first element fine\", \"gain power isn't ok\"]",
]
;
verbs
| partition by verb
{
    data
    | mv-apply input = parse_json(json) on(
        where input startswith verb
    )
    | project ['id'], json
}
id json
1 ["Discover me", "some text"]
3 ["first element fine", "gain power isn't ok"]
user_78618070
Related