I am wondering if there are any list-to-list filters in KQL?
Some of my columns in some of my tables are dynamic JSON lists.
I'd like to be able to filter out records by doing some validations against those lists.
To do this I've created a few tools using existing operators:
// If column (of type array) has any of values[]
Table | where array_length(set_intersect(col, values)) > 0
// If column (of type array) has all of values[]
Table | where array_length(set_intersect(col, values)) == array_length(values)
// If column (of type array) has none of values[]
Table | where array_length(set_intersect(col, values)) == 0
It would be really cool if there was a shorthand for this, maybe something like:
Table | where any(col, values)
Table | where all(col, values)
Table | where none(col, values)
You can use has_any() operator and has_all() operator to filter records based on the presence of values in a list. In order to return the rows where column does not contain any of the values in the values list, you can use not() operator to the result of has_any() operator.
Query:
let values = dynamic(["a","b","i"]);
let mytable=datatable (id:int, col:dynamic)
[
1, dynamic(["a", "b", "c"]),
2, dynamic(["e", "f"]),
3, dynamic(["i", "j", "k"])
];
mytable | where col has_any (values);
mytable | where col has_all (values);
mytable | where not(col has_any (values));