Can i create custom queries in Where and Case statements from dynamic array in Kusto Query?

Question

I have a requests datatable with url, name, timestamp columns, to which i add another computed column operationType.

I want to filter and query requests based on defined dynamic object attributes data, in order to obtain Operations and their usage count, like this:

operationType    | Counts
-------------------------
1.1 Operation A  | 2400
-------------------------
1.2 Operation B  | 1500

What i have:

let daysAgo = 100d;
let data = dynamic([
{
    'searchKey': 'url',
    'searchOperator': 'contains',
    'searchValue': 'AjaxContent?option=something',
    'operationName': '1.1 Operation A'
},
{
    'searchKey': 'name',
    'searchOperator': 'matches regex',
    'searchValue': 'POST /api/repo/\\d+/filters',
    'operationName': '1.2 Operation B'
}]);

let req = requests
| where (timestamp >= ago(daysAgo))
| where 

//Issue #1: I want data[0].searchKey to be used as the requests column name
data[0].searchKey contains data[0].searchValue 
//url contains data[0].searchValue //works

//Issue #2: 'doesn't work, error 'matches regex' operator requires string arguments
or name matches data[1].searchValue
//or name matches regex 'POST /api/repo/\\d+/filters' //works

| extend operationType= 
            case(name == data[2].searchValue, data[2].operationName,
                    url contains data[0].searchValue, data[0].operationName
                    'Other - please check'  ) 
| order by timestamp desc;

req | summarize Counts = count() by operationType

The ideal would be to create a custom query, like bellow (and maybe even looping through dynamic array attributes data) (Q #3):

requests | where data[0].searchKey data[0].searchOperator data[0].searchValue

In the same context, ideal would also be to extend the computed operationType column to be somehow custom created (Q #4):

requests
| where
...
| extend operationType= case(url contains data[0].searchValue, data[0].operationName,
                             name matches regex data[0].searchValue, data[0].operationName)

or

requests
| where
...
| extend operationType= case(data[0].searchKey data[0].searchOperator data[0].searchValue, data[0].operationName,
                             data[1].searchKey data[1].searchOperator data[0].searchValue, data[0].operationName)

1. Can dynamic value be used as the datatable column name in a where statement (Issue #1)?
2. Can dynamic value be used as right hand in the matches regex statement (Issue #2)?
3. Can create a dynamically custom query where statement, looping through dynamic array (Q #3)?
4. Can a computed extended column be custom created in a case statement (Q #4)?

Answer 1

1) Can dynamic value be used as the datatable column name in a where statement?

Yes. Please check : https://learn.microsoft.com/en-us/azure/kusto/query/columnifexists
Example showing column_ifexists() and 'matches regex'

datatable (a:string, b:string)
['1', '2']
| where column_ifexists('a', '') matches regex '1'

2) Can dynamic value be used as right hand in the matches regex statement?

To some extent. In general, only constant values are supported, so you can't use row-context (where regex will vary for each row). You can, however, use toscalar() to calculate constant regular expressions as a sub-query, and use those as an argument to 'matches regex'. https://learn.microsoft.com/en-us/azure/kusto/query/toscalarfunction

3) Can create a dynamically custom query where statement, looping through dynamic array?

No. Dynamic KQL queries are not supported at this moment.

4) Can a computed extended column be custom created in a case statement?

Yes, as long as you use techniques described in answer #1 and #2, and not running into limitations of #3.