How to query array column with array parameter in Azure Data Explorer (kusto)

2
votes

I have table with dynamic column where I store list of IDs and I have parameter where list of IDs can be passed. So, I want to get rows where any of input values present in table column. Something like this:

declare query_parameters (        
    i_ids: dynamic = dynamic([15,33,37])
);
let T = datatable(id: int, ids:dynamic)
[
    1, dynamic([10, 15, 18]),
    2, dynamic([22,25,29]),
    3, dynamic([31, 33, 37]),
];
T
| where ids has_any(i_ids);

I need to get rows 1 and 3 but It fails with message: The source expression is of type 'dynamic' and cannot be compared with numeric arguments. Can you please help me write proper query?

1
azure-data-explorerkql

1 Answers

2
votes

you can try using set_intersect(): https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/setintersectfunction

declare query_parameters (        
    i_ids: dynamic = dynamic([15,33,37])
);
let T = datatable(id: int, ids:dynamic)
[
    1, dynamic([10, 15, 18]),
    2, dynamic([22,25,29]),
    3, dynamic([31, 33, 37]),
];
T
| where array_length(set_intersect(ids, i_ids)) > 0