I have an API that executes some KQL.
I'm executing a KQL that filters all rows such that some column (that is of type list of string) contains any of the values in some given list of strings.
Basically:
let foo1 = datatable(d: dynamic) [
dynamic([""]),
dynamic(["foobar"]),
dynamic(["Alice", "Bob"]),
dynamic(["Alice"])
];
foo1
| where d has_any (dynamic(["Alice", "otherthing"]))
Which outputs the last 2 rows as I expect - I wanted all rows where d
has any of the values "Alice", "otherthing"
.
Everything works as expected, until I want all rows that contain the empty string value:
let foo1 = datatable(d: dynamic) [
dynamic([""]),
dynamic(["foobar"]),
dynamic(["Alice", "Bob"]),
dynamic(["Alice"])
];
foo1
| where d has_any (dynamic([""]))
and the result above outputs all rows, instead of only the first row, which has the empty string value.
- Why?
- What can I change in order for it to work as I expect it?
CodePudding user response:
1.
Apparently any string has the empty string, e.g.:
print "abc" has ""
print_0 |
---|
true |
2.
It seems you are looking for a full match and not a partial match.
In this case, has
might lead to undesired results, e.g.:
print dynamic(["The story of Alice in wonderland", "Bob"]) has "Alice"
print_0 |
---|
true |
Solution:
let foo1 = datatable(d: dynamic) [
dynamic([""]),
dynamic(["foobar"]),
dynamic(["Alice", "Bob"]),
dynamic(["Alice"])
];
foo1
| where array_length(set_intersect(dynamic(["Alice", "otherthing"]), d)) > 0
d |
---|
["Alice","Bob"] |
["Alice"] |
let foo1 = datatable(d: dynamic) [
dynamic([""]),
dynamic(["foobar"]),
dynamic(["Alice", "Bob"]),
dynamic(["Alice"])
];
foo1
| where array_length(set_intersect(dynamic([""]), d)) > 0
d |
---|
[""] |