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

Question

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?

Yoni L. · Accepted Answer · 2020-04-24 18:20:24Z

2

you can try using set_intersect(): https://learn.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

edited Apr 24, 2020 at 18:20

answered Apr 24, 2020 at 16:21

Yoni L.

26.5k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related